Passing DDL value to bit field in SQL

I have a DDL with two values: 0 and 1. When I use

sqlcommand.Parameters.Add(New SqlParameter("@.fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@.fld").Value = System.Boolean.Parse(DropDownList1.SelectedValue)

or

sqlcommand.Parameters.Add(New SqlParameter("@.fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@.fld").Value = System.Convert.ToBoolean(DropDownList1.SelectedValue)

to insert the selected value in a sql2k database BIT field I am given this error:

System.FormatException: String was not recognized as a valid Boolean.

How can I solve this problem?

// C# but you should get the idea
bool trueFalse = false;
if( DropDownList1.SelectedValue.Value.Equals("1") ){
 trueFalse = true;
}
sqlcommandl.Parameters("@.fld").Value = trueFalse;

Passing a subquery as a parameter to a user defined function

I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code =
@.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
@.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
@.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...

quote:

---

> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to

---

the

quote:

---

> url. When I retrieve the value for the "beds" checkbox list from the url

---

it

quote:

---

> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it

---

gives

quote:

---

> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)

---

AND

quote:

---

> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND

---

(@.a9

quote:

---

> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,

---

Archive_Flag,

quote:

---

> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,

---

Property_Rental_Period,

quote:

---

kred">
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code

---

=

quote:

---

> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
> @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
> @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>

---

|||Fabio Papa (fapapa@.hotmail.com) writes:

quote:

---

> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded

---

Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.

quote:

---

> CREATE PROCEDURE [sp_SearchResults_v2]

---

Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code = @.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code = @.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code = @.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...
> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
the
> url. When I retrieve the value for the "beds" checkbox list from the url
it
> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it
gives
> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)
AND
> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND
(@.a9
> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,
Archive_Flag,
> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,
Property_Rental_Period,
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code
=> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code => @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code => @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>|||Fabio Papa (fapapa@.hotmail.com) writes:
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded
Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.
> CREATE PROCEDURE [sp_SearchResults_v2]
Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Partitions and Common Practice

Hello All,
I posted a simillar question sometime back but I did not see any response. I
have modified my question quite a bit with more info and posting it hoping to
see some response this time :)
I have a Policy Master data file whose uniqueness is identified by the
following
columns (Business Key).
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction was entered into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
We loaded this file into the Staging table of SQLServer 2005 database --
STGPOLICY. The data in this staging table, STGPOLICY, will be processed and
populated into FACPOLICY table.
STGPOLICY Table has these 5 important columns apart from the other columns
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date]
Surrogate Key, UniqID, is an identity column that gets incremented as
records are inserted into the table.
For better performance and Maintainability, we decided to use Partitions on
FACPOLICY table with [Transaction Date] being the Partition Key.
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
For faster loading and processing, I have to Partition the STGPOLICY table
on [Transaction Date] column as this column is used for extensive
search/processing. So, I created the partition with [TransactionDate] as the
Partition column. However, when I try to create a unique index on the natural
keys the system gives an error as Transactiondate is not part of the Unique
Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
Date].
I went through the BOL and found that this is the ugly side of PArtitions.
Does that mean that I cannot define a primary key on the natural key when the
natural key is not a Partition Key? I'm left with either creating a nonunique
key or including the Partition Key along with the Natural Key. Is this a
common practice?
Now, with the FACPOLICY table, the UniqID adds another level of intrigue
since the Primary Key would be the UniqID (assuming that there is no
Partitions). With partitions, would it mean that I have to include
TransactionDate to UniqID to create a Primary Key ?
I'm looking for some common practices. Please help.
Thanks,
rgn
Hello All,
Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working on
Partitioning some of the tables for a datawarehousing project and need some
guidance/help.
I have a Policy Master table whose uniqueness is identified by the following
columns via a Primary Key Clustered constraint.
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction came into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
I created a Partition Function, Partition Scheme and the Created the table
on the Partition Scheme with the [Transaction Date] as the Partitioning
Column. Things looked good till this point. However, when I tried to create
the Primary Key on the above 4 columns, I got the following error:
Msg 1908, Level 16, State 1, Line 1
Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors
I understand what this error means. But I cannot modify the Primary Key by
adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
help?
Thanks,
rgn> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
Another other alternative is to not partition the natural key. The downside
is that you can't use SWITCH to quickly load data with the non-partitioned
index in place. If SWITCH is an important part of you partitioning
strategy, you can drop the non-partitioned unique constraint/index, SWITCH
in the new data and then recreate the constraint/index afterward.
I wouldn't go as far as to say that it is a common practice to add the
partitioning column to a unique constraint/index solely to facilitate
partitioning, but I suspect it is not all that unusual. Bending the
database design rules in favor of performance and manageability is an option
as long as you have mechanisms in place to ensure data integrity.
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
All partitioned indexes include the partitioning column and unique
partitioned indexes must explicitly include the partitioning column in the
key. The partitioning column is implicitly included in non-clustered
indexes (but not part of the key) if not already part of the clustered or
non-clustered key. So, like the natural key, you'll need to add the
partitioning column to the surrogate key to form a composite surrogate key.
It's a little more inconvenient to deal with the composite key but more
palatable that adding the partitioning key to a natural key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:7B2D7BEE-F322-46B0-8CB4-4493C7C7E86B@.microsoft.com...
> Hello All,
> I posted a simillar question sometime back but I did not see any response.
> I
> have modified my question quite a bit with more info and posting it hoping
> to
> see some response this time :)
> I have a Policy Master data file whose uniqueness is identified by the
> following
> columns (Business Key).
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction was entered into the system and since it is
> a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> We loaded this file into the Staging table of SQLServer 2005 database --
> STGPOLICY. The data in this staging table, STGPOLICY, will be processed
> and
> populated into FACPOLICY table.
> STGPOLICY Table has these 5 important columns apart from the other columns
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date]
> Surrogate Key, UniqID, is an identity column that gets incremented as
> records are inserted into the table.
> For better performance and Maintainability, we decided to use Partitions
> on
> FACPOLICY table with [Transaction Date] being the Partition Key.
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
>
> For faster loading and processing, I have to Partition the STGPOLICY table
> on [Transaction Date] column as this column is used for extensive
> search/processing. So, I created the partition with [TransactionDate] as
> the
> Partition column. However, when I try to create a unique index on the
> natural
> keys the system gives an error as Transactiondate is not part of the
> Unique
> Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
> Date].
> I went through the BOL and found that this is the ugly side of PArtitions.
> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
> I'm looking for some common practices. Please help.
>
> Thanks,
> rgn
>
>
> Hello All,
> Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working
> on
> Partitioning some of the tables for a datawarehousing project and need
> some
> guidance/help.
> I have a Policy Master table whose uniqueness is identified by the
> following
> columns via a Primary Key Clustered constraint.
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction came into the system and since it is a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> I created a Partition Function, Partition Scheme and the Created the table
> on the Partition Scheme with the [Transaction Date] as the Partitioning
> Column. Things looked good till this point. However, when I tried to
> create
> the Primary Key on the above 4 columns, I got the following error:
> Msg 1908, Level 16, State 1, Line 1
> Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
> Partition columns for a unique index must be a subset of the index key.
> Msg 1750, Level 16, State 0, Line 1
> Could not create constraint. See previous errors
> I understand what this error means. But I cannot modify the Primary Key by
> adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
> help?
> Thanks,
> rgn
>