Hello,
Can anyone solve this prob...
BACKGROUND:
I have a column in my query that is calculated from money datatypes. To
avoid the divide by zero error I created a nice inline case statement that
places something else in that field instead. It works great with any number
of my choice. For example I can put 100 everywhere that this calculated
field would've had a divide by zero error. HOWEVER, I want to put the word
'NA' in that field when this happens. But I can't because the datatype is
wrong:
ERROR:
Hence the error:Implicit conversion from data type char to money is not
allowed.
PROBLEM:
So is there a way to legally cast or convert so that I can put 'NA' in this
field instead of a number like zero or a hundred?
Here is my attempt so far (doesn't work):
CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
expression]
Thanks.
--
Alex A.You will need to cast the entire field as varchar.
Another option would be to return NULL when the expression = 0 and have the
presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
NA.
"Alex A." <AlexA@.discussions.microsoft.com> wrote in message
news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
> Hello,
> Can anyone solve this prob...
> BACKGROUND:
> I have a column in my query that is calculated from money datatypes. To
> avoid the divide by zero error I created a nice inline case statement that
> places something else in that field instead. It works great with any
> number
> of my choice. For example I can put 100 everywhere that this calculated
> field would've had a divide by zero error. HOWEVER, I want to put the
> word
> 'NA' in that field when this happens. But I can't because the datatype is
> wrong:
> ERROR:
> Hence the error:Implicit conversion from data type char to money is not
> allowed.
> PROBLEM:
> So is there a way to legally cast or convert so that I can put 'NA' in
> this
> field instead of a number like zero or a hundred?
> Here is my attempt so far (doesn't work):
> CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
> expression]
> Thanks.
> --
> Alex A.
>|||WKidd,
Thanks for the post...
I can't cast the whole field because if it is not a divide by zero situation
I need the dollar amount to calculate... But I like the Null idea. Anyone
have the syntax in mind for that? Can I just put the word NULL after the
THEN statement?
"WKidd" wrote:
> You will need to cast the entire field as varchar.
> Another option would be to return NULL when the expression = 0 and have th
e
> presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
> NA.
> "Alex A." <AlexA@.discussions.microsoft.com> wrote in message
> news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
>
>|||Look up hopw to use a NULL in SQL. But I would also get rid of the
MONEY datatype.
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||For divisions, I usually write:
SELECT A / NULLIF(B, 0)
This will yield NULL if B is 0, because Anything / NULL yields NULL.
Regarding the precision of the money data type, you may want to do an
implicit (or an explicit) conversion to the decimal (or the float) data
type, like this:
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SELECT CAST((1. * @.my_part / @.total_amt) * @.gross_amt AS money)
The correct result would have been 13530.5038673170731707317073170(...)
but converted back to the money datatype it is 13530.5039
Razvan
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs
|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>
|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message[vbcol=seagreen]
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?
>
>.
>
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs
|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>
|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message[vbcol=seagreen]
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?
>
>.
>
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
An identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
An identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsBest shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?[vbcol=seagreen]
>
>.
>
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsBest shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?[vbcol=seagreen]
>
>.
>
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsHi ,
i think i knowit. it's like that AutoNumber in MS
Access where a row number will be created for each row
rdgs
>--Original Message--
>Hi Ppl,
> Could i know what's the purpose of the IDENTITY
COLUMN ?
>thks ^ rdgs
>.
>|||Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
--
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> > Hi Ppl,
> >
> > Could i know what's the purpose of the IDENTITY COLUMN ?
> >
> > thks ^ rdgs
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
>> Best shown through an example:
>> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY
(1,1))
>> GO
>> INSERT #IDTest (AChar) VALUES ('A')
>> INSERT #IDTest (AChar) VALUES ('B')
>> INSERT #IDTest (AChar) VALUES ('C')
>> GO
>> SELECT *
>> FROM #IDTest
>> ORDER BY IDCol
>> --
>> We seeded the identity with a start value of 1, and
increment each row by
>1.
>> (IDENTITY(1,1)). Every row we insert is now given a
unique, sequential
>ID.
>> So the 'A' row has an ID of 1, the 'B' row 2, and
the 'C' row 3. This ID
>> column can be used to track order of INSERTs or more
commonly as a
>surrogate
>> key for joining to other tables.
>> Number one thing to remember: Never rely on the
IDENTITY being
>consecutive!
>> Rows can be deleted, transactions can be rolled back,
and the identity can
>> be re-seeded. So there is certainly no guarantee that
there won't
>> eventually be gaps.
>> You should also always make sure when using the
IDENTITY as a primary key
>> that you have other constraints in place to ensure
uniqueness of your
>data.
>> Mis-use of IDENTITY columns for primary keys is a very
common source of
>data
>> integrity problems. So use them wearily.
>>
>> "maxzsim" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>> > Hi Ppl,
>> >
>> > Could i know what's the purpose of the IDENTITY
COLUMN ?
>> >
>> > thks ^ rdgs
>>
>
>.
>
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsHi ,
i think i knowit. it's like that AutoNumber in MS
Access where a row number will be created for each row
rdgs
>--Original Message--
>Hi Ppl,
> Could i know what's the purpose of the IDENTITY
COLUMN ?
>thks ^ rdgs
>.
>|||Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
--
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> > Hi Ppl,
> >
> > Could i know what's the purpose of the IDENTITY COLUMN ?
> >
> > thks ^ rdgs
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
>> Best shown through an example:
>> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY
(1,1))
>> GO
>> INSERT #IDTest (AChar) VALUES ('A')
>> INSERT #IDTest (AChar) VALUES ('B')
>> INSERT #IDTest (AChar) VALUES ('C')
>> GO
>> SELECT *
>> FROM #IDTest
>> ORDER BY IDCol
>> --
>> We seeded the identity with a start value of 1, and
increment each row by
>1.
>> (IDENTITY(1,1)). Every row we insert is now given a
unique, sequential
>ID.
>> So the 'A' row has an ID of 1, the 'B' row 2, and
the 'C' row 3. This ID
>> column can be used to track order of INSERTs or more
commonly as a
>surrogate
>> key for joining to other tables.
>> Number one thing to remember: Never rely on the
IDENTITY being
>consecutive!
>> Rows can be deleted, transactions can be rolled back,
and the identity can
>> be re-seeded. So there is certainly no guarantee that
there won't
>> eventually be gaps.
>> You should also always make sure when using the
IDENTITY as a primary key
>> that you have other constraints in place to ensure
uniqueness of your
>data.
>> Mis-use of IDENTITY columns for primary keys is a very
common source of
>data
>> integrity problems. So use them wearily.
>>
>> "maxzsim" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>> > Hi Ppl,
>> >
>> > Could i know what's the purpose of the IDENTITY
COLUMN ?
>> >
>> > thks ^ rdgs
>>
>
>.
>
Purpose od IDENTITY column
Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Wednesday, March 21, 2012
purchase order query: very funny and challenge ^_^
i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004
i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number
SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"
i checked my database, last record is LP-0545381-0300
in my debuging process, surprisingly found that selected record is LP-0545381-301 !
any one hav any suggestion? ^_^
I fail to see the humor here, but that's irrelevant. It's difficult to know the query behavior without knowing what GetYearCode() is supposed to return and how it relates to the data in your table. Also, why does your last record (LP-0545381-301) have only 3 digits at the end when your format suggests that it should contain 4 digits? Finally, what were you expecting the result to be?|||database never had that record, but selected out, isnt it very funny...anyway, GetYearCode() will return the middle code as0545381, i also suprisingly why return 3 digit?!unreasonable, isnt it
what i expected result isLP-0545381-0300
Tuesday, March 20, 2012
Pulling out table and column descriptions
When I run the following query:
Select * From Information_Schema.columns Where TABLE_NAME = 'Answers'
I see that, for my table "Answers", I have 9 rows returned, showing each
field in my table.
The sysobjects query shows where I can get the Id for Answers and use it for
getting the field descriptions from sysproperties (along with a table
description):
Select * From sysobjects Where name = 'Answers'
Select * From sysproperties Where id = 859150106
The trouble I am having is I want to join the the column and sysproperties
tables together, but I get back 38 rows showing a lot of duplicate data when
I only want the 9 rows that define my Answers table. Can someone show and
explain to me what I am doing wrong? Below is the query I tried that returne
d
38 rows instead of the 9 I wanted. Thank you.
Select isc.table_name, isc.column_name, sp.value
From sysproperties sp
Join Information_Schema.columns isc On sp.smallid = isc.ordinal_position
Where isc.table_name = 'Answers'Hello, Mike
Try something like this:
SELECT c.name as ColumnName, p.value as Description
FROM syscolumns c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysproperties p
ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
WHERE o.name='YourTable' ORDER BY c.colid
Razvan|||Exactly what I wanted. Thanks.
"Razvan Socol" wrote:
> Hello, Mike
> Try something like this:
> SELECT c.name as ColumnName, p.value as Description
> FROM syscolumns c
> INNER JOIN sysobjects o ON c.id=o.id
> LEFT JOIN sysproperties p
> ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
> WHERE o.name='YourTable' ORDER BY c.colid
> Razvan
>
Select * From Information_Schema.columns Where TABLE_NAME = 'Answers'
I see that, for my table "Answers", I have 9 rows returned, showing each
field in my table.
The sysobjects query shows where I can get the Id for Answers and use it for
getting the field descriptions from sysproperties (along with a table
description):
Select * From sysobjects Where name = 'Answers'
Select * From sysproperties Where id = 859150106
The trouble I am having is I want to join the the column and sysproperties
tables together, but I get back 38 rows showing a lot of duplicate data when
I only want the 9 rows that define my Answers table. Can someone show and
explain to me what I am doing wrong? Below is the query I tried that returne
d
38 rows instead of the 9 I wanted. Thank you.
Select isc.table_name, isc.column_name, sp.value
From sysproperties sp
Join Information_Schema.columns isc On sp.smallid = isc.ordinal_position
Where isc.table_name = 'Answers'Hello, Mike
Try something like this:
SELECT c.name as ColumnName, p.value as Description
FROM syscolumns c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysproperties p
ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
WHERE o.name='YourTable' ORDER BY c.colid
Razvan|||Exactly what I wanted. Thanks.
"Razvan Socol" wrote:
> Hello, Mike
> Try something like this:
> SELECT c.name as ColumnName, p.value as Description
> FROM syscolumns c
> INNER JOIN sysobjects o ON c.id=o.id
> LEFT JOIN sysproperties p
> ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
> WHERE o.name='YourTable' ORDER BY c.colid
> Razvan
>
pulling ntext values sql server 2005
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
>
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
>
>
>
>
>
>
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173717649.377685.103280@.h3g2000cwc.googlegro ups.com...
Yeah.. if you're only inserting 200.. ;-)
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
just to see the max we one had it's always 200.
Thanks.
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
just to see the max we one had it's always 200.
>
Thanks.
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Hi,
I've tried both Sql sever 2000 QA and Sql server 2005 Management
Studio. For QA i set the Results Max chars to 8192 and in Management
studio i set the max chars per column for Text Retults to 8192 and for
Grid to Non XML 65535, but it's still not returning the full, and each
max length is always 200.
On Mar 12, 12:11 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"phil2phil" <philtwop...@.yahoo.comwrote in message
>
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
>
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
Quote:
Originally Posted by
just to see the max we one had it's always 200.
>
Quote:
Originally Posted by
Thanks.
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
On Mar 12, 12:26 pm, "phil2phil" <philtwop...@.yahoo.comwrote:
Quote:
Originally Posted by
Hi,
I've tried both Sql sever 2000 QA and Sql server 2005 Management
Studio. For QA i set the Results Max chars to 8192 and in Management
studio i set the max chars per column for Text Retults to 8192 and for
Grid to Non XML 65535, but it's still not returning the full, and each
max length is always 200.
>
On Mar 12, 12:11 pm, "Greg D. Moore \(Strider\)"
>
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"phil2phil" <philtwop...@.yahoo.comwrote in message
>
Quote:
Originally Posted by
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
>
Quote:
Originally Posted by
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
Quote:
Originally Posted by
Quote:
Originally Posted by
just to see the max we one had it's always 200.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Thanks.
>
Quote:
Originally Posted by
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
Quote:
Originally Posted by
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173717649.377685.103280@.h3g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
>I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
>
Yeah.. if you're only inserting 200.. ;-)
(note that text fields sometimes are better handled with readtext/writetext.
But if you're moving to SQL 2005 any time I'd highly recommend varchar(max)
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Pulling Data into one column
I don't know if this is possible, but here goes;
I need to pull sales data out of SQL into Access or Excel to run in a Pivot Table. I am currently doing this by querying SQL with an Access Database and then imoprting that data into Excel to pivot. It takes literally hours to refresh this pivot. I would like to create a SQL View to help speed this process along. The problem is that Debits and Credits are both put into my SQL database as positive numbers. I am pulling only these two types of sales entries.
What I would like to do is this: iif(dbo.table.type = "credit", dbo.table.dollaramount, dbo.table.dollaramount * -1)
This would pull all of my credits as positive numbers and my debits as negative numbers. Is there a way to write this in a SQL query instead of using Access?
Something like this, using CASE:
Code Snippet
SELECT MyField = CASE
WHEN dbo.table.type = 'Credit' THEN ( dbo.table.dollaramount * (-1) )
ELSE dbo.table.dollaramount
ELSE
FROM MyTable
Excellent! With a little massaging, this seems to be what I am needing. This is what I used:
Code Snippet
SELECT table.type, table.docnumbr, table.itemnmbr, table.itemdesc, 'GROSSSALE' =
CASE
WHEN type = 4 THEN (table.XTNDPRCE * (-1) )
ELSE (table.XTNDPRCE)
END
from table
where type = 3 or type = 4
I think I am going to try to incorporate an Inner Join now.
Again, thanks for your help with this.
-Jody
Pulling data from tables using ColumnID
My problem is I need to loop through columns in a table and refer to each column using its ColumnID, all this I can do, I can even pull back the Cloumn Name from the system tables, but I can't find a way of pulling the data from this into a variable...
The code below will give me the data I need, but I don't know how to put the result from the exec statement into a variable?
select @.sql = 'select ' + name + ' from Test'
from syscolumns
where colid = @.i and id = object_id('Test')
exec(@.sql)
Please help
thanks,
ConanHi,
you might use
exec sp_executesql <cmd>, <paramlist>, <parameters>
this will allow you to get the result from some cmd like
'select @.Data='+@.columname+' from '+@.tablename
have a look into bol|||Hi,
Unfortunately this didn't work...
It has the result of only returning the column name again, rather than its data, have been stumped now on this little thing for a couple of days. If you can help please do, thanks,
DECLARE @.i int
DECLARE @.colname nvarchar(200)
SET @.i = 5
--Gets the column name of the column we want using its Column ID
select @.colname = name from syscolumns where colid = @.i and id = object_id('Test')
--Why does the below merely return the column name rather than its data
select @.colname from test
--The below ruturns the correct value, but I can't/don't know how to store it.
DECLARE @.sql nvarchar(1000)
DECLARE @.data int
select @.sql = 'select ' + @.colname + ' from Test'
EXEC sp_executesql @.sql
go
thanks,
Conan|||Hi,
as i said before, you can use sp_executesql. read bol for details...
you have to distinguish between vars and their values. your "select @.colname from test" will select whatever is the value of colname, regardless the table you specify! even without a table name the cmd will show your var value.
declare @.Table sysname, @.ID int, @.ColName nvarchar(200), @.CMD nvarchar(1000), @.ColData varchar(20)
select @.Table='test', @.ID=5
-- get column name for table / colid
select @.ColName=name from syscolumns where colid=@.ID and id= object_id(@.Table)
-- build up cmd string for pulling data
select @.CMD='select @.Param = '+@.ColName+' from '+@.Table
-- show what we have so far
print '@.Table = >'+@.Table+'<'
print '@.COLNAME = >'+@.ColName+'<'
print '@.CMD= >'+@.CMD+'<'
-- execute the cmd string pulling data from @.colname into @.ColData via @.Param
exec sp_executesql @.CMD, N'@.Param varchar(20) output', @.Param = @.ColData output
-- here we are
print '@.COLDATA= >'+@.COLDATA+'<'
The code below will give me the data I need, but I don't know how to put the result from the exec statement into a variable?
select @.sql = 'select ' + name + ' from Test'
from syscolumns
where colid = @.i and id = object_id('Test')
exec(@.sql)
Please help
thanks,
ConanHi,
you might use
exec sp_executesql <cmd>, <paramlist>, <parameters>
this will allow you to get the result from some cmd like
'select @.Data='+@.columname+' from '+@.tablename
have a look into bol|||Hi,
Unfortunately this didn't work...
It has the result of only returning the column name again, rather than its data, have been stumped now on this little thing for a couple of days. If you can help please do, thanks,
DECLARE @.i int
DECLARE @.colname nvarchar(200)
SET @.i = 5
--Gets the column name of the column we want using its Column ID
select @.colname = name from syscolumns where colid = @.i and id = object_id('Test')
--Why does the below merely return the column name rather than its data
select @.colname from test
--The below ruturns the correct value, but I can't/don't know how to store it.
DECLARE @.sql nvarchar(1000)
DECLARE @.data int
select @.sql = 'select ' + @.colname + ' from Test'
EXEC sp_executesql @.sql
go
thanks,
Conan|||Hi,
as i said before, you can use sp_executesql. read bol for details...
you have to distinguish between vars and their values. your "select @.colname from test" will select whatever is the value of colname, regardless the table you specify! even without a table name the cmd will show your var value.
declare @.Table sysname, @.ID int, @.ColName nvarchar(200), @.CMD nvarchar(1000), @.ColData varchar(20)
select @.Table='test', @.ID=5
-- get column name for table / colid
select @.ColName=name from syscolumns where colid=@.ID and id= object_id(@.Table)
-- build up cmd string for pulling data
select @.CMD='select @.Param = '+@.ColName+' from '+@.Table
-- show what we have so far
print '@.Table = >'+@.Table+'<'
print '@.COLNAME = >'+@.ColName+'<'
print '@.CMD= >'+@.CMD+'<'
-- execute the cmd string pulling data from @.colname into @.ColData via @.Param
exec sp_executesql @.CMD, N'@.Param varchar(20) output', @.Param = @.ColData output
-- here we are
print '@.COLDATA= >'+@.COLDATA+'<'
Monday, March 12, 2012
Pull Merge Replication - Expand Column Size
I need to increase the size of a column in a table which is part of merge
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
Subscribe to:
Posts (Atom)