Char and Varchar: process

Showing posts with label process. Show all posts

Tuesday, March 20, 2012

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004
You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
David Portas
SQL Server MVP

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
--
David Portas
SQL Server MVP
--

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
..
David Portas
SQL Server MVP
--sql

Check Foreign Key integrity of existing data

Hi,
I'm in the process of writing a script that inserts or updates default data
for a database.
I came to the conclusion that I have to temporarily disable certain foreign
keys.
At the end of the script however, I'd like to check existing data to verify
that everything is still ok.
Basically something like this:
ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
-- Insert data here
ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
existing data, right?
-- Process other tables here
-- At this point I'd like to check that the FK_MyForeignKey constaint is
valid for existing data
Now I know that I could do something like this:
ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
However, is there a way to check existing data without dropping and
re-adding the constraint?
Or is dropping and re-adding not that costly?
Thanks,
Erik> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
Right.

> However, is there a way to check existing data without dropping and
> re-adding the constraint?
Use an IF EXISTS (or similar query)...test prior to running the ALTER
TABLE...CHECK statement.
HTH
Jerry
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message
news:eGZJSnZ1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm in the process of writing a script that inserts or updates default
> data
> for a database.
> I came to the conclusion that I have to temporarily disable certain
> foreign
> keys.
> At the end of the script however, I'd like to check existing data to
> verify
> that everything is still ok.
> Basically something like this:
> ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
> -- Insert data here
> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
> -- Process other tables here
> -- At this point I'd like to check that the FK_MyForeignKey constaint is
> valid for existing data
>
> Now I know that I could do something like this:
> ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
> ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
>
> However, is there a way to check existing data without dropping and
> re-adding the constraint?
> Or is dropping and re-adding not that costly?
> Thanks,
> Erik
>|||OK, so manually run a check.
Didn't even think of that. :)
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
check
> Right.
>
> Use an IF EXISTS (or similar query)...test prior to running the ALTER
> TABLE...CHECK statement.
> HTH
> Jerry|||You can also check out DBCC CHECKCONSTRAINTS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message news:OVnT3Gb1FHA.1212@.TK2MSFTNGP10.phx
.gbl...
> OK, so manually run a check.
> Didn't even think of that. :)
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> check
>sql

Monday, March 19, 2012

Check file for being used by some other process when using Flat File Source

I am wondering how easy is to check for file locks and have our SSIS Package to wait until file has been release by the process which is using it.

Also, same question when we're writing to a Flat File (or Flat File Destination).

Thanks,I think best way is to use script task, in while loop check for file info, if it is locked continue if not then break or set some variable stating that file is unlocked now.

Check duplicates when inserting

hi all,
I am confuse now,Let me explain the process.
In database table called "Individual " it's store all customer information
user can insert in this table by .NET application but we have another process
that can insert individual from file.
Sometimes same individual repeat again so before i insert into Individual
table i have to check that it's exist or not but there is not any unique ID
for each customer so basically one customer in individual table have
Indentity generated IndividualID so when i try to insert there is no way to
find out that is this same customer or diffrent.
Indivivual table have another column like
FirstName
LastName
BirthDay
BirthMonth
BirthYear
Gender
Maritial Status
Street
StreetName
AddressLine1
City
PostalCode
So how can i find that this is duplicate or not.
I can actually match this column value with inserting individual value but
you know coustomer can registered with little diffrent with names or address
like ("daniel" can register with second time with "dan")
So is there is any trick to compare this duplicates.
Thanks in advance
SoHi
Itzik Ben-Gan has written a greate example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> hi all,
> I am confuse now,Let me explain the process.
> In database table called "Individual " it's store all customer information
> user can insert in this table by .NET application but we have another
process
> that can insert individual from file.
> Sometimes same individual repeat again so before i insert into Individual
> table i have to check that it's exist or not but there is not any unique
ID
> for each customer so basically one customer in individual table have
> Indentity generated IndividualID so when i try to insert there is no way
to
> find out that is this same customer or diffrent.
> Indivivual table have another column like
> FirstName
> LastName
> BirthDay
> BirthMonth
> BirthYear
> Gender
> Maritial Status
> Street
> StreetName
> AddressLine1
> City
> PostalCode
> So how can i find that this is duplicate or not.
> I can actually match this column value with inserting individual value but
> you know coustomer can registered with little diffrent with names or
address
> like ("daniel" can register with second time with "dan")
> So is there is any trick to compare this duplicates.
> Thanks in advance
>
> So|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Thx for your reply.
But i want to check existing record with inserting record by columns of that
individual table to find out this is unique record or same
"Uri Dimant" wrote:
> Hi
> Itzik Ben-Gan has written a greate example
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > hi all,
> >
> > I am confuse now,Let me explain the process.
> >
> > In database table called "Individual " it's store all customer information
> > user can insert in this table by .NET application but we have another
> process
> > that can insert individual from file.
> >
> > Sometimes same individual repeat again so before i insert into Individual
> > table i have to check that it's exist or not but there is not any unique
> ID
> > for each customer so basically one customer in individual table have
> > Indentity generated IndividualID so when i try to insert there is no way
> to
> > find out that is this same customer or diffrent.
> >
> > Indivivual table have another column like
> > FirstName
> > LastName
> > BirthDay
> > BirthMonth
> > BirthYear
> > Gender
> > Maritial Status
> > Street
> > StreetName
> > AddressLine1
> > City
> > PostalCode
> >
> > So how can i find that this is duplicate or not.
> >
> > I can actually match this column value with inserting individual value but
> > you know coustomer can registered with little diffrent with names or
> address
> > like ("daniel" can register with second time with "dan")
> >
> > So is there is any trick to compare this duplicates.
> >
> > Thanks in advance
> >
> >
> > So
>
>|||Hi Yomesh
I think i understood your problem, for this you can write an INSTEAD of
trigger:
Here is the sample Code:
==============CREATE TRIGGER <TRIGGER>
on <TABLE> instead of insert
as
begin
declare
@.cnt integer
@.fname
@.lname...
SELECT @.cnt = count(*)
FROM <TABLE>,INSERTED
WHERE <TABEL>.FirstName = INSERTED.FirstName AND
<TABEL>.LastName = INSERTED.LastName
IF ISNULL(@.cnt,0) = 0
begin
select @.fname=FirstName, @.LName = LastName...
FROM INSERTER
insert into <TABLE> values (@.FName,@.LName,.....)
end
end
go
==============The Syntax might not be correct, but this gives an idea how to go about.
I dont have SQL installed on my system to give u the exact system. If u
still have any problems please get back to me and I will try to address
thanks and regards
Chandra
"yomesh" wrote:
> Thx for your reply.
> But i want to check existing record with inserting record by columns of that
> individual table to find out this is unique record or same
> "Uri Dimant" wrote:
> > Hi
> > Itzik Ben-Gan has written a greate example
> >
> > CREATE TABLE #Demo (
> > idNo int identity(1,1),
> > colA int,
> > colB int
> > )
> >
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (1,6)
> > INSERT INTO #Demo(colA,colB) VALUES (2,4)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (4,2)
> > INSERT INTO #Demo(colA,colB) VALUES (3,3)
> > INSERT INTO #Demo(colA,colB) VALUES (5,1)
> > INSERT INTO #Demo(colA,colB) VALUES (8,1)
> >
> > PRINT 'Table'
> > SELECT * FROM #Demo
> >
> > PRINT 'Duplicates in Table'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo <> B.idNo
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Duplicates to Delete'
> > SELECT * FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > DELETE FROM #Demo
> > WHERE idNo IN
> > (SELECT B.idNo
> > FROM #Demo A JOIN #Demo B
> > ON A.idNo < B.idNo -- < this time, not <>
> > AND A.colA = B.colA
> > AND A.colB = B.colB)
> >
> > PRINT 'Cleaned-up Table'
> > SELECT * FROM #Demo
> >
> > DROP TABLE #Demo
> >
> > "Yomesh" <Yomesh@.discussions.microsoft.com> wrote in message
> > news:740A33F5-59AF-465A-A543-E724A49951C1@.microsoft.com...
> > > hi all,
> > >
> > > I am confuse now,Let me explain the process.
> > >
> > > In database table called "Individual " it's store all customer information
> > > user can insert in this table by .NET application but we have another
> > process
> > > that can insert individual from file.
> > >
> > > Sometimes same individual repeat again so before i insert into Individual
> > > table i have to check that it's exist or not but there is not any unique
> > ID
> > > for each customer so basically one customer in individual table have
> > > Indentity generated IndividualID so when i try to insert there is no way
> > to
> > > find out that is this same customer or diffrent.
> > >
> > > Indivivual table have another column like
> > > FirstName
> > > LastName
> > > BirthDay
> > > BirthMonth
> > > BirthYear
> > > Gender
> > > Maritial Status
> > > Street
> > > StreetName
> > > AddressLine1
> > > City
> > > PostalCode
> > >
> > > So how can i find that this is duplicate or not.
> > >
> > > I can actually match this column value with inserting individual value but
> > > you know coustomer can registered with little diffrent with names or
> > address
> > > like ("daniel" can register with second time with "dan")
> > >
> > > So is there is any trick to compare this duplicates.
> > >
> > > Thanks in advance
> > >
> > >
> > > So
> >
> >
> >

Tuesday, March 20, 2012

Check if Column is property type before Alter Statement

Check if Column is property type before Alter Statement

Check if Column is property type before Alter Statement

Check Foreign Key integrity of existing data

Monday, March 19, 2012

Check file for being used by some other process when using Flat File Source

Check duplicates when inserting

Check duplicates when inserting

Check duplicates when inserting

Char and Varchar

Blog Archive

About Me