Char and Varchar: specific

Showing posts with label specific. Show all posts

Monday, March 19, 2012

Check for existence of specific value in a dataset

How do I check for the existence of a specific value in a column in a
dataset? For example, I have 'dataset' with 'columnA' and I want to find out
if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
but I'm having trouble. Thanks.
StephanieOn May 22, 12:42 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> How do I check for the existence of a specific value in a column in a
> dataset? For example, I have 'dataset' with 'columnA' and I want to find out
> if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
> but I'm having trouble. Thanks.
> Stephanie
The closest thing to the functionality you want is with and expression
similar to this:
=Max(iif(Fields!columnA.Value = 'valueA', 1, 0))
So if the value exists in the column values, the expression will
return a 1. Otherwise, it will return a 0.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Check for column & insert

Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

Hi,

Yes it is possible. This is an example (SQL 2005) that adds the column NewColumn to the Person.Contact table (AdventureWorks) if the column does not exists.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Person'
AND TABLE_NAME = 'Contact'
AND COLUMN_NAME = 'NewColumn')
BEGIN
ALTER TABLE Person.Contact
ADD NewColumn int
END

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Great thanks,

got that working now...

Sunday, March 11, 2012

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:
>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:
>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Thursday, February 16, 2012

Chart

Hi everyone,
I am currently using SSRS 2000. I have a chart that shows everything that we have shipped by specific part number. The Y axis displays the quantity and X axis shows the date. Well, the X axis I grouped it by year then by month, so it can display Jan 04, Dec 05, Mar 07, etc. but all I get is the year. How can I format it, or do so it can display that format??

Thanks ahead of time,
Abner

try this expression

=MonthName(Month(Fields!FeildName.Value),true)& " " & Year(Fields!Fieldname.Value)

|||

Thank you very much, works like a charm.

Abner

Sunday, February 12, 2012

Char Fields Everywhere!

Hi,
We are using Oracle/PeopleSoft CRM on SQL2K. I see only char fields are used
every. Varchar fields are NOT used. Is there any specific reason for it. Can
someone through some light on this subject. I am just curious.
Thanks in advance.
--SeequellWhoever wrote the app doesn't know what they're doing?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Seequell" <Seequell@.discussions.microsoft.com> wrote in message
news:A97D507B-7E93-4F17-8FC6-49E4700DE752@.microsoft.com...
> Hi,
> We are using Oracle/PeopleSoft CRM on SQL2K. I see only char fields are
> used
> every. Varchar fields are NOT used. Is there any specific reason for it.
> Can
> someone through some light on this subject. I am just curious.
> Thanks in advance.
> --Seequell|||I would like to explore more before coming to that conclusion...
--Seequell
"Adam Machanic" wrote:
> Whoever wrote the app doesn't know what they're doing?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Seequell" <Seequell@.discussions.microsoft.com> wrote in message
> news:A97D507B-7E93-4F17-8FC6-49E4700DE752@.microsoft.com...
> > Hi,
> > We are using Oracle/PeopleSoft CRM on SQL2K. I see only char fields are
> > used
> > every. Varchar fields are NOT used. Is there any specific reason for it.
> > Can
> > someone through some light on this subject. I am just curious.
> > Thanks in advance.
> > --Seequell
>
>|||JDEdwards is the same way (at least the one place I've been that used it
was...)
Perhaps a leftover from an application that was ported from yesteryear, when
varchars were not allowed, or form a different platform (mainframe?)
Unfortunately, I;m not familiar with the history of PeopleSoft CRM...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Seequell" <Seequell@.discussions.microsoft.com> wrote in message
news:A3348B42-5AB1-473C-80E7-634DD96D663C@.microsoft.com...
>I would like to explore more before coming to that conclusion...
> --Seequell
>
> "Adam Machanic" wrote:
>> Whoever wrote the app doesn't know what they're doing?
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Seequell" <Seequell@.discussions.microsoft.com> wrote in message
>> news:A97D507B-7E93-4F17-8FC6-49E4700DE752@.microsoft.com...
>> > Hi,
>> > We are using Oracle/PeopleSoft CRM on SQL2K. I see only char fields are
>> > used
>> > every. Varchar fields are NOT used. Is there any specific reason for
>> > it.
>> > Can
>> > someone through some light on this subject. I am just curious.
>> > Thanks in advance.
>> > --Seequell
>>|||I am not familiar with mainframe...
Is varchar field is not allowed in mainframe?
Thanks.
--Seequell
"Kevin3NF" wrote:
> JDEdwards is the same way (at least the one place I've been that used it
> was...)
> Perhaps a leftover from an application that was ported from yesteryear, when
> varchars were not allowed, or form a different platform (mainframe?)
> Unfortunately, I;m not familiar with the history of PeopleSoft CRM...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Seequell" <Seequell@.discussions.microsoft.com> wrote in message
> news:A3348B42-5AB1-473C-80E7-634DD96D663C@.microsoft.com...
> >I would like to explore more before coming to that conclusion...
> >
> > --Seequell
> >
> >
> > "Adam Machanic" wrote:
> >
> >> Whoever wrote the app doesn't know what they're doing?
> >>
> >>
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> http://www.datamanipulation.net
> >> --
> >>
> >>
> >> "Seequell" <Seequell@.discussions.microsoft.com> wrote in message
> >> news:A97D507B-7E93-4F17-8FC6-49E4700DE752@.microsoft.com...
> >> > Hi,
> >> > We are using Oracle/PeopleSoft CRM on SQL2K. I see only char fields are
> >> > used
> >> > every. Varchar fields are NOT used. Is there any specific reason for
> >> > it.
> >> > Can
> >> > someone through some light on this subject. I am just curious.
> >> > Thanks in advance.
> >> > --Seequell
> >>
> >>
> >>
>
>

Char Fields Everywhere!

Friday, February 10, 2012

Changing User Passwords

Hello,
I am hoping that someone can assist me. Some users have have left and I need
to change the the passwords on a specific Databse. How can I do this?
Ever Learning Net Admin
Use Enterprise Manager - Logins or use sp_password.
HTH
JErry
"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin
|||"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin
Do you really need to change their password, or should you be removing those
user accounts.
As Jerry said, Enterprise Manager, or sp_password stored procedure will do
the trick.
Rick Sawtell
|||I need to change the password because of how are website is tied into the
username and password. This is a long story but in short bad programming that
I am working to resolve. Where in enterprise manager do I change it? I have
looked and cannot find it. The only thing it seems I can change is the
permissions from what I can see.
(I am new to SQL. I am received my MCDBA 2 years a go but then never worked
on another SQL DB. I have forgotten too much.)
Ever Learning Net Admin
"Rick Sawtell" wrote:

> "Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
> news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Do you really need to change their password, or should you be removing those
> user accounts.
> As Jerry said, Enterprise Manager, or sp_password stored procedure will do
> the trick.
> Rick Sawtell
>
>
|||Look under security, there you will see Logins. It will be at the server
level not the database level.
-- Jesse
"Randaddyz" wrote:
[vbcol=seagreen]
> I need to change the password because of how are website is tied into the
> username and password. This is a long story but in short bad programming that
> I am working to resolve. Where in enterprise manager do I change it? I have
> looked and cannot find it. The only thing it seems I can change is the
> permissions from what I can see.
> (I am new to SQL. I am received my MCDBA 2 years a go but then never worked
> on another SQL DB. I have forgotten too much.)
> --
> Ever Learning Net Admin
>
> "Rick Sawtell" wrote:

Changing User Passwords

Hello,
I am hoping that someone can assist me. Some users have have left and I need
to change the the passwords on a specific Databse. How can I do this?
--
Ever Learning Net AdminUse Enterprise Manager - Logins or use sp_password.
HTH
JErry
"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin|||"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin
Do you really need to change their password, or should you be removing those
user accounts.
As Jerry said, Enterprise Manager, or sp_password stored procedure will do
the trick.
Rick Sawtell|||I need to change the password because of how are website is tied into the
username and password. This is a long story but in short bad programming that
I am working to resolve. Where in enterprise manager do I change it? I have
looked and cannot find it. The only thing it seems I can change is the
permissions from what I can see.
(I am new to SQL. I am received my MCDBA 2 years a go but then never worked
on another SQL DB. I have forgotten too much.)
--
Ever Learning Net Admin
"Rick Sawtell" wrote:
> "Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
> news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> > Hello,
> >
> > I am hoping that someone can assist me. Some users have have left and I
> > need
> > to change the the passwords on a specific Databse. How can I do this?
> > --
> > Ever Learning Net Admin
> Do you really need to change their password, or should you be removing those
> user accounts.
> As Jerry said, Enterprise Manager, or sp_password stored procedure will do
> the trick.
> Rick Sawtell
>
>|||Look under security, there you will see Logins. It will be at the server
level not the database level.
-- Jesse
"Randaddyz" wrote:
> I need to change the password because of how are website is tied into the
> username and password. This is a long story but in short bad programming that
> I am working to resolve. Where in enterprise manager do I change it? I have
> looked and cannot find it. The only thing it seems I can change is the
> permissions from what I can see.
> (I am new to SQL. I am received my MCDBA 2 years a go but then never worked
> on another SQL DB. I have forgotten too much.)
> --
> Ever Learning Net Admin
>
> "Rick Sawtell" wrote:
> >
> > "Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
> > news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> > > Hello,
> > >
> > > I am hoping that someone can assist me. Some users have have left and I
> > > need
> > > to change the the passwords on a specific Databse. How can I do this?
> > > --
> > > Ever Learning Net Admin
> >
> > Do you really need to change their password, or should you be removing those
> > user accounts.
> >
> > As Jerry said, Enterprise Manager, or sp_password stored procedure will do
> > the trick.
> >
> > Rick Sawtell
> >
> >
> >

Changing User Passwords

Hello,
I am hoping that someone can assist me. Some users have have left and I need
to change the the passwords on a specific Databse. How can I do this?
--
Ever Learning Net AdminUse Enterprise Manager - Logins or use sp_password.
HTH
JErry
"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin|||"Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Hello,
> I am hoping that someone can assist me. Some users have have left and I
> need
> to change the the passwords on a specific Databse. How can I do this?
> --
> Ever Learning Net Admin
Do you really need to change their password, or should you be removing those
user accounts.
As Jerry said, Enterprise Manager, or sp_password stored procedure will do
the trick.
Rick Sawtell|||I need to change the password because of how are website is tied into the
username and password. This is a long story but in short bad programming tha
t
I am working to resolve. Where in enterprise manager do I change it? I have
looked and cannot find it. The only thing it seems I can change is the
permissions from what I can see.
(I am new to SQL. I am received my MCDBA 2 years a go but then never worked
on another SQL DB. I have forgotten too much.)
--
Ever Learning Net Admin
"Rick Sawtell" wrote:

> "Randaddyz" <Randaddyz@.discussions.microsoft.com> wrote in message
> news:D03A32CE-6944-4158-ACCC-048D3E81AB5A@.microsoft.com...
> Do you really need to change their password, or should you be removing tho
se
> user accounts.
> As Jerry said, Enterprise Manager, or sp_password stored procedure will do
> the trick.
> Rick Sawtell
>
>|||Look under security, there you will see Logins. It will be at the server
level not the database level.
-- Jesse
"Randaddyz" wrote:
[vbcol=seagreen]
> I need to change the password because of how are website is tied into the
> username and password. This is a long story but in short bad programming t
hat
> I am working to resolve. Where in enterprise manager do I change it? I hav
e
> looked and cannot find it. The only thing it seems I can change is the
> permissions from what I can see.
> (I am new to SQL. I am received my MCDBA 2 years a go but then never worke
d
> on another SQL DB. I have forgotten too much.)
> --
> Ever Learning Net Admin
>
> "Rick Sawtell" wrote:
>

Monday, March 19, 2012

Check for existence of specific value in a dataset

Check for column & insert

Sunday, March 11, 2012

CHECK Constraint to prevent a conditional duplicate

Thursday, February 16, 2012

Chart

Sunday, February 12, 2012

Char Fields Everywhere!

Char Fields Everywhere!

Char Fields Everywhere!

Friday, February 10, 2012

Changing User Passwords

Changing User Passwords

Changing User Passwords

Char and Varchar

Blog Archive

About Me