How to show all the fields in a dataset.

I have a sp that returns a different dataset depending on the parameter
values, it is dyanamic sp which has different field names and also the
number of fields.
Is there a way to incorporate this in reporting services, like in .Net
we can display the dataset by just binding it to the datagrid and
letting datagrid handle the rest.
Thanks,
TonyNo. RS does not work that way. It expects a consistent number/name of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107965810.679249.274270@.c13g2000cwb.googlegroups.com...
> I have a sp that returns a different dataset depending on the parameter
> values, it is dyanamic sp which has different field names and also the
> number of fields.
> Is there a way to incorporate this in reporting services, like in .Net
> we can display the dataset by just binding it to the datagrid and
> letting datagrid handle the rest.
> Thanks,
> Tony
>|||Thanks for the prompt reply Bruce, Is there any other workaround for
the issue I have any ideas or suggestions would be great.
Thanks again,
Tony|||You can have multiple datasets in a report, each of them calling the
procedure with the appropriate parameter. This does mean the SP will get
called multiple times. Then you should be able to hide the table on the
report if there is no data. I haven't tried this but I think you can. You
still might end up with some blank lines though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107967055.899883.298640@.f14g2000cwb.googlegroups.com...
> Thanks for the prompt reply Bruce, Is there any other workaround for
> the issue I have any ideas or suggestions would be great.
> Thanks again,
> Tony
>|||You could also at the end of your proc pivot (or is this really
unpivoting) the data. So for a table with structure
# keycol # col1 # col2 # col3 # col4 # ... # col<n> #
you would end up with
# keycolvalue # colname # colvalue #
this will have n rows for each row in the original table. You may want
to modify your proc to build that to begin with rather than pivot data
later.
then you could drop that into a matrix.
This would work fairly well if all variable columns are of the same
type. Otherwise you'd have to do some formatting in the stored
procedure.

How to show all field names and data in two columns i.e. Pivot / c

What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'
Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/...pts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'
sql

How to show all field names and data in two columns i.e. Pivot / c

What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id =
'123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical...ipts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id =
> '123'

How to show all field names and data in two columns i.e. Pivot / c

What we would like to do is to pivot a table with 300 columns into one that
is just two columns. The first column containing the field name and the
second column containing the value of that column.
This also includes a where clause to filter the recordset. e.g where id = '123'Hi
I think you are actually looking to UNPIVOT the data such as
http://www.umachandar.com/technical/SQL6x70Scripts/Main25.htm
You will probably be better doing this client side.
John
"Smartbiz" <Smartbiz@.discussions.microsoft.com> wrote in message
news:35458430-363D-42C7-B3C0-879EE294ED1B@.microsoft.com...
> What we would like to do is to pivot a table with 300 columns into one
> that
> is just two columns. The first column containing the field name and the
> second column containing the value of that column.
> This also includes a where clause to filter the recordset. e.g where id => '123'

How to setup permissions by using field value?

Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.User Roles doesn't help here.
The database roles does not help here.
The best way to filter the records horizontally is to implement stored procedures where "fldgeocenter" would be an input parameter. Use the value of that param. to filter the records in the WHERE statement.

Originally posted by dperrott
Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.

How to set up auto incrementing for a field

I have a database table. I need to set a particular data field to allow for Auto Incrementing. Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by.

What I have done so far is to right click on the field name in SQL Server 2005. Then, on the pop-up menu I select the menu option for properties.

When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields. In other words, if I try to type text in this field, nothing happens. The fields I am talking about are:

Identity Increment

Identity Seed

Is Identity

Does anyone know what the problem is? What do I have to do to edit these fields.

Check if you have the necessary permissions.|||You can not change the properties directly in the Column Properties window, instead, you can right click the column and choose 'Modify'. Enjoy it

how to set the default value

I have database in sql server. One field called datesent, i would like to set the default value as now(), i just type in the default value in database design, it didn't work. Please help, many thanks.try this: getdate()|||It works, thank you very much!|||I use the getdate() as the default value. But i found the value like "
11/8/2005 10:14:00 AM. How can i set the default value like the format as:
11/8/2005. Thanks.|||hi,

try this

create table yourTable
(
ColumnName datetime default convert(varchar,getdate(),107)
)

How to set the default value

I want to use SQL to set the default of a field.
How can I implement with DDL?
ad
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME DEAFULT GETDATE()
)
INSERT INTO #Test (col1) VALUES (1)
GO
SELECT * FROM #Test
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I want to use SQL to set the default of a field.
> How can I implement with DDL?
>
|||CREATE TABLE Testdata
(
TestIDCOlumn INT,
TestColumn varchar(10) DEFAULT 'Test'
)
Insert into Testdata(TestIDColumn) Values (1)
Select * from Testdata
Drop Table Testdata
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>I want to use SQL to set the default of a field.
> How can I implement with DDL?
>
|||Thank,
If the TestColumn have create before, but did not set default value yet.
Can I modify the structrue now , set the default value to that column?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> gl
sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE Testdata
> (
> TestIDCOlumn INT,
> TestColumn varchar(10) DEFAULT 'Test'
> )
> Insert into Testdata(TestIDColumn) Values (1)
> Select * from Testdata
> Drop Table Testdata
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>
|||alter table TestData
add constraint DF_TestDate_TestColumn
default ('Test') for TestColumn
HTH,
Gert-Jan
ad wrote:[vbcol=seagreen]
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> gl
> sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
|||You would probably have to
rename the column
add a new column with default value
move data from orig column to new column
drop orig column
rename new column to orig name
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23BW3EA%23XFHA.4036@.tk2msftngp13.phx.gbl...
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> gl
> sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
>

How to set the default value

I want to use SQL to set the default of a field.
How can I implement with DDL?ad
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME DEAFULT GETDATE()
)
INSERT INTO #Test (col1) VALUES (1)
GO
SELECT * FROM #Test
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||CREATE TABLE Testdata
(
TestIDCOlumn INT,
TestColumn varchar(10) DEFAULT 'Test'
)
Insert into Testdata(TestIDColumn) Values (1)
Select * from Testdata
Drop Table Testdata
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||Thank,
If the TestColumn have create before, but did not set default value yet.
Can I modify the structrue now , set the default value to that column?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l
¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE Testdata
> (
> TestIDCOlumn INT,
> TestColumn varchar(10) DEFAULT 'Test'
> )
> Insert into Testdata(TestIDColumn) Values (1)
> Select * from Testdata
> Drop Table Testdata
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> >I want to use SQL to set the default of a field.
> > How can I implement with DDL?
> >
> >
>|||alter table TestData
add constraint DF_TestDate_TestColumn
default ('Test') for TestColumn
HTH,
Gert-Jan
ad wrote:
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l
> ¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> > CREATE TABLE Testdata
> > (
> > TestIDCOlumn INT,
> > TestColumn varchar(10) DEFAULT 'Test'
> > )
> >
> > Insert into Testdata(TestIDColumn) Values (1)
> >
> > Select * from Testdata
> >
> > Drop Table Testdata
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> > "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> > news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> > >I want to use SQL to set the default of a field.
> > > How can I implement with DDL?
> > >
> > >
> >
> >|||You would probably have to
rename the column
add a new column with default value
move data from orig column to new column
drop orig column
rename new column to orig name
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23BW3EA%23XFHA.4036@.tk2msftngp13.phx.gbl...
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> ¼¶¼g©ó¶l
> ¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> CREATE TABLE Testdata
>> (
>> TestIDCOlumn INT,
>> TestColumn varchar(10) DEFAULT 'Test'
>> )
>> Insert into Testdata(TestIDColumn) Values (1)
>> Select * from Testdata
>> Drop Table Testdata
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
>> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>> >I want to use SQL to set the default of a field.
>> > How can I implement with DDL?
>> >
>> >
>>
>

How to set the default value

I want to use SQL to set the default of a field.
How can I implement with DDL?ad
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME DEAFULT GETDATE()
)
INSERT INTO #Test (col1) VALUES (1)
GO
SELECT * FROM #Test
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||CREATE TABLE Testdata
(
TestIDCOlumn INT,
TestColumn varchar(10) DEFAULT 'Test'
)
Insert into Testdata(TestIDColumn) Values (1)
Select * from Testdata
Drop Table Testdata
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||Thank,
If the TestColumn have create before, but did not set default value yet.
Can I modify the structrue now , set the default value to that column?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> gl
sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE Testdata
> (
> TestIDCOlumn INT,
> TestColumn varchar(10) DEFAULT 'Test'
> )
> Insert into Testdata(TestIDColumn) Values (1)
> Select * from Testdata
> Drop Table Testdata
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>|||alter table TestData
add constraint DF_TestDate_TestColumn
default ('Test') for TestColumn
HTH,
Gert-Jan
ad wrote:[vbcol=seagreen]
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> g
l
> sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...|||You would probably have to
rename the column
add a new column with default value
move data from orig column to new column
drop orig column
rename new column to orig name
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23BW3EA%23XFHA.4036@.tk2msftngp13.phx.gbl...
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> gl
> sD:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
>

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (3
2
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is ther
e
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this
a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> >I have a calculation in a stored procedure that returns a percentage like
> >(32
> > * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> > float.
> > I need to set or convert the result to have 2 decimal places like 1.86,
> > rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> > variations (examples from the internet), nothing is working for me. Is
> > there
> > a way to achieve this in SQL? I would really appreciate the help.
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set null value for a smalldatetime inside a Script Component task?

how the hell you allocate a null value for a smalldatetime sql field?

Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:

.Parameters("@.FecEnajenacion").Value = "1999-01-01"

error:

.Parameters("@.FecEnajenacion").Value = vbNull

.Parameters("@.FecEnajenacion").Value = Null

.Parameters("@.FecEnajenacion").Value = SqlDbType.?

Try

Parameters("@.FecEnajenacion").Value = Nothing

-Jamie

|||

hi jamie,

Well, it doesn't works because of my code wait any value:

Public Overrides Sub PreExecute()

sqlCmd = New SqlCommand(sSql, sqlConn)

sqlParam = New SqlParameter("@.FecEnajenacion", SqlDbType.SmallDateTime)

sqlCmd.Parameters.Add(sqlParam)

....

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

With Sqlcmd

If dFecha2 = "0000/00/00" Then

.Parameters("@.FecEnajenacion").Value = Nothing

Else

.Parameters("@.FecEnajenacion").Value = dFecha2

End If

.ExecuteNonQuery()

End With

End Sub

Prepared statement '(@.Ejercicio smallint,@.NIFPerc char(9),@.NIFRep char(9),@.Nombre va' expects parameter @.FecEnajenacion, which was not supplied.

|||enric,

the DBNull data type must be used when setting the parameter value property to null.

i recommend that you post this question to the ADO.NET forum for further assistance.|||thank you

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...[vbc
ol=seagreen]
> Can we use SQL to set a field of a table is not allow null and it's defaul
t.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to
1.
> How can I wrte this SQL?
>[/vbcol]

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?
CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

How to set empty a space from a derived column expression?

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?

TIA

What is the data type of [Column 12]?

-Jamie

|||

At destination?

[char](1) COLLATE Modern_Spanish_CI_AS NULL,

|||

No. In the pipeline.

If it is DT_STR then your expression will be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If it is DT_WSTR then it'll be

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

-Jamie

|||

I've got string [DT_STR] as datatype in my flat file definition and string [DT_WSTR] as datatype column on the right 'Expression' field (Derived Column)

I've allocated as you said this line:

[Column 12] == "0" ? [Column 12] : NULL(DT_WSTR,1)

And remains in black, ok but when I launch the package appears this:

Error at Camara Recibida [OLE DB Destination [9902]]: Columns "Derived Column 1_12" and "Rein" cannot convert between unicode and non-unicode string data types.

Error at Camara Recibida [DTS.Pipeline]: "component "OLE DB Destination" (9902)" failed validation and returned validation status "VS_ISBROKEN".

Error at Camara Recibida [DTS.Pipeline]: One or more component failed validation.

Error at Camara Recibida: There were errors during task validation.

|||

So you are inputting a DT_STR but outputting a DT_WSTR? Is that correct? That seems like a strange thing to do to me but of course, it is entirely up to you. If it were me I would use the same type on the output as on the input - but it is your choice.

If you want to output a DT_STR then the expression should be:

[Column 12] == "0" ? [Column 12] : NULL(DT_STR,1,1252)

If you want to output a DT_WSTR then the expression should be:

[Column 12] == "0" ? (DT_WSTR, 1)[Column 12] : NULL(DT_WSTR,1)

-Jamie

P.S. Please try and include all relevant information when posting a problem. If you are trying to do some data conversion then one of the most obvious things to state is the types of the input columns and what types you require the output to have.

|||

That's fine now.

Thanks a lot indeed for your help.

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>