I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.sql
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Tuesday, March 27, 2012
altering tables and constraints
I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary
Please do not post the same message to multiple newsgroups independently.
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary
Please do not post the same message to multiple newsgroups independently.
altering tables and constraints
I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-GaryPlease do not post the same message to multiple newsgroups independently.
Thursday, February 9, 2012
All about the PIVOT - HELP!!
Hi all,
I have a problem whereby i need to convert multiple row data into a single
row for instance if i have a table
CliID Code V D T
1 A 100
1 B 01/01/06
1 C Beer
2 A 50
2 C Milk
I would want to return this as
CliID A-V A-D A-T B-V B-D B-T C-V C-D C-T
1 100 01/01/06 Beer
2 50 Milk
I have been able to achieve this using the follow code
select CliID,
max(case when code = 'A' then V else 0 end) as A-V,
max(case when code = 'A' then D else null end) as A-D,
max(case when code = 'A' then T else null end) as A-T,
max(case when code = 'B' then V else 0 end) as B-V,
max(case when code = 'B' then D else null end) as B-D,
max(case when code = 'B' then T else null end) as B-T
max(case when code = 'C' then V else 0 end) as C-V,
max(case when code = 'C' then D else null end) as C-D,
max(case when code = 'C' then T else null end) as C-T
from casdet
group by CliID
which works fine however we are now using sql 2005 and i thought it may be
more efficient to make use of the PIVOT function everyone is talking about.
I
can get a basic pivot working for instance pivoting on the code and
sumarising a column say the V column, however i cannot get it to add in the
additional columns.. the code i am currently using looks like this
select CliID, [A] as A, [C] as C
from
(select CliID, code, V, T from casdet) p
Pivot(
max (V)
for code in
( [A], [C])
) as pvt
This pivots the codes and summarised the V column, for all codes. What i
want to do is pivot the codes as column headers then for each code display
the associated V D and T columns all on a single row.
I know this may sound a little confusing so to summarise.. I have a table
with ID, Code, Value, Date, Text columns.. for a given id i need to produce
a
single row where the code and each of its associated Value, Date, and Text
columns appear on a single row. So hopefully my result set would look simila
r
to
code A Code B Code C
ID V D T V D T V D T
1 1 - - - X - - - A
2 4 - C - Z - - - F
I would like to use the pivot command if possible to do this.
Thanks
Iancan you post the ddl, insert script for sample data and the result for the
sample data?|||ok this is going to seem like a very stupid question but how do i do this.
The data i have given in the above is all made up by way of an example. But
in order to assist I have done a select into statement on the live data to
another table. I can script this but when i do so, all i get is the create
table element or the insert script (depending which one i choose) but i
cannot seem to get it to generate a script to create the table with the
data.. I am sure this was possible when i last used SQL several years back.
"Omnibuzz" wrote:
> can you post the ddl, insert script for sample data and the result for the
> sample data?|||Well,
To answer to this post, have a look at this..
http://www.aspfaq.com/etiquette.asp?id=5006
And, anyways, I tried to create the table with the sample data and tried to
get the results.
But looks like you are better off in using your old syntax than using PIVOT.
PIVOT doesn't address this issue you are facing since you are pivoting on th
e
code and grouping on CliId, we it get complicated and results in lots of
nesting..
Anyways if you want to use that then it goes this way.
select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
from casdet pivot (
max(T) for code in ([a],[b],[c])
) as T,
casdet pivot (
max(D) for code in ([a],[b],[c])
) as D,
casdet pivot (
max(V) for code in ([a],[b],[c])
) as V
where t.cliid = d.cliid and d.cliid = v.cliid
group by T.CliID
And it performs slower :)
Hope this helps.|||Thanks for the reply and the info on how to create the DDL and sample data..
It will help a lot. The only reason i wanted to use the PIVOT method over th
e
older way was I figured that with it being a new feature in SQL2005 it would
be optimised and provide better performance than the older way.
Thanks again
Ian
"Omnibuzz" wrote:
> Well,
> To answer to this post, have a look at this..
> http://www.aspfaq.com/etiquette.asp?id=5006
> And, anyways, I tried to create the table with the sample data and tried t
o
> get the results.
> But looks like you are better off in using your old syntax than using PIVO
T.
> PIVOT doesn't address this issue you are facing since you are pivoting on
the
> code and grouping on CliId, we it get complicated and results in lots of
> nesting..
> Anyways if you want to use that then it goes this way.
>
> select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
> ,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
> max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
> from casdet pivot (
> max(T) for code in ([a],[b],[c])
> ) as T,
> casdet pivot (
> max(D) for code in ([a],[b],[c])
> ) as D,
> casdet pivot (
> max(V) for code in ([a],[b],[c])
> ) as V
> where t.cliid = d.cliid and d.cliid = v.cliid
> group by T.CliID
> And it performs slower :)
> Hope this helps.
>
I have a problem whereby i need to convert multiple row data into a single
row for instance if i have a table
CliID Code V D T
1 A 100
1 B 01/01/06
1 C Beer
2 A 50
2 C Milk
I would want to return this as
CliID A-V A-D A-T B-V B-D B-T C-V C-D C-T
1 100 01/01/06 Beer
2 50 Milk
I have been able to achieve this using the follow code
select CliID,
max(case when code = 'A' then V else 0 end) as A-V,
max(case when code = 'A' then D else null end) as A-D,
max(case when code = 'A' then T else null end) as A-T,
max(case when code = 'B' then V else 0 end) as B-V,
max(case when code = 'B' then D else null end) as B-D,
max(case when code = 'B' then T else null end) as B-T
max(case when code = 'C' then V else 0 end) as C-V,
max(case when code = 'C' then D else null end) as C-D,
max(case when code = 'C' then T else null end) as C-T
from casdet
group by CliID
which works fine however we are now using sql 2005 and i thought it may be
more efficient to make use of the PIVOT function everyone is talking about.
I
can get a basic pivot working for instance pivoting on the code and
sumarising a column say the V column, however i cannot get it to add in the
additional columns.. the code i am currently using looks like this
select CliID, [A] as A, [C] as C
from
(select CliID, code, V, T from casdet) p
Pivot(
max (V)
for code in
( [A], [C])
) as pvt
This pivots the codes and summarised the V column, for all codes. What i
want to do is pivot the codes as column headers then for each code display
the associated V D and T columns all on a single row.
I know this may sound a little confusing so to summarise.. I have a table
with ID, Code, Value, Date, Text columns.. for a given id i need to produce
a
single row where the code and each of its associated Value, Date, and Text
columns appear on a single row. So hopefully my result set would look simila
r
to
code A Code B Code C
ID V D T V D T V D T
1 1 - - - X - - - A
2 4 - C - Z - - - F
I would like to use the pivot command if possible to do this.
Thanks
Iancan you post the ddl, insert script for sample data and the result for the
sample data?|||ok this is going to seem like a very stupid question but how do i do this.
The data i have given in the above is all made up by way of an example. But
in order to assist I have done a select into statement on the live data to
another table. I can script this but when i do so, all i get is the create
table element or the insert script (depending which one i choose) but i
cannot seem to get it to generate a script to create the table with the
data.. I am sure this was possible when i last used SQL several years back.
"Omnibuzz" wrote:
> can you post the ddl, insert script for sample data and the result for the
> sample data?|||Well,
To answer to this post, have a look at this..
http://www.aspfaq.com/etiquette.asp?id=5006
And, anyways, I tried to create the table with the sample data and tried to
get the results.
But looks like you are better off in using your old syntax than using PIVOT.
PIVOT doesn't address this issue you are facing since you are pivoting on th
e
code and grouping on CliId, we it get complicated and results in lots of
nesting..
Anyways if you want to use that then it goes this way.
select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
from casdet pivot (
max(T) for code in ([a],[b],[c])
) as T,
casdet pivot (
max(D) for code in ([a],[b],[c])
) as D,
casdet pivot (
max(V) for code in ([a],[b],[c])
) as V
where t.cliid = d.cliid and d.cliid = v.cliid
group by T.CliID
And it performs slower :)
Hope this helps.|||Thanks for the reply and the info on how to create the DDL and sample data..
It will help a lot. The only reason i wanted to use the PIVOT method over th
e
older way was I figured that with it being a new feature in SQL2005 it would
be optimised and provide better performance than the older way.
Thanks again
Ian
"Omnibuzz" wrote:
> Well,
> To answer to this post, have a look at this..
> http://www.aspfaq.com/etiquette.asp?id=5006
> And, anyways, I tried to create the table with the sample data and tried t
o
> get the results.
> But looks like you are better off in using your old syntax than using PIVO
T.
> PIVOT doesn't address this issue you are facing since you are pivoting on
the
> code and grouping on CliId, we it get complicated and results in lots of
> nesting..
> Anyways if you want to use that then it goes this way.
>
> select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
> ,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
> max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
> from casdet pivot (
> max(T) for code in ([a],[b],[c])
> ) as T,
> casdet pivot (
> max(D) for code in ([a],[b],[c])
> ) as D,
> casdet pivot (
> max(V) for code in ([a],[b],[c])
> ) as V
> where t.cliid = d.cliid and d.cliid = v.cliid
> group by T.CliID
> And it performs slower :)
> Hope this helps.
>
Alignment problem
After i upload the report to server , and try to convert it to excel, the alignment of report is out!!the width of columns is different with the width i set in design..=.= however, the report is pretty when i preview in reporting servcise design.
Anyone face same problem?
Hi
this is probabily because of the page size u specified for the report in the report deisigner.
right click on the empty part of the report designer go to properties and in the layout tab u will have page width and page height.
Make shure that that the left ,right margin plus the report body width is less than the page width you are going to set same is the case with height.
If you dont get your problem solved by this please let me know.
Vamsi Krishna Korasiga
|||nope..i 'd set my table rows' height to 0.11in..
when i preview n export to excel, it exactly same with what i do in designer.
but when i uploaded to server n export , the report rows change to 0.15
it cause i cannot fix all rows into one page..
N i wonder in the report properties>layout , there is field "columns" and "spacing"....
what is the use?would it affect my report row's height?
Alignment problem
After i upload the report to server , and try to convert it to excel, the alignment of report is out!!the width of columns is different with the width i set in design..=.= however, the report is pretty when i preview in reporting servcise design.
Anyone face same problem?
Hi
this is probabily because of the page size u specified for the report in the report deisigner.
right click on the empty part of the report designer go to properties and in the layout tab u will have page width and page height.
Make shure that that the left ,right margin plus the report body width is less than the page width you are going to set same is the case with height.
If you dont get your problem solved by this please let me know.
Vamsi Krishna Korasiga
|||nope..i 'd set my table rows' height to 0.11in..
when i preview n export to excel, it exactly same with what i do in designer.
but when i uploaded to server n export , the report rows change to 0.15
it cause i cannot fix all rows into one page..
N i wonder in the report properties>layout , there is field "columns" and "spacing"....
what is the use?would it affect my report row's height?
Subscribe to:
Posts (Atom)