How can I write a stored procedure with SQL 2000 Server to check an e-mail and password entered by the user, and if both exist select the Id field ?
Table Users:
UserId
First_name
Surname
Age
E-mail
Password
If it is possible I would like to know how can I write the error messages in three cases: If E-mail doesn't exist, if password doesnt exist and if both doesnt exist. And I would like that those messages would be able to appear on the website application.
ThanksMay check Planet SC (http://planet-source-code.com) for any CE.|||Ok thank you
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Check if Delete ran in a trigger
I am new to SQL Server and I am trying to write a trigger where I am doing a
delete on another table. I need to know whether this delete fails or not.
How can I achieve this?
TIA
Altmancheck the rowcount in the trigger
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||What do you mean by fail? If the delete fails with an error message, like a
foreign key constraint
violation, the trigger will not be fired. If you mean modify zero rows, chec
k @.@.ROWCOUNT the very
first thing you do in the triggers, or check the number of rows in the delet
ed table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.
gbl...
>I am new to SQL Server and I am trying to write a trigger where I am doing
a delete on another
>table. I need to know whether this delete fails or not. How can I achieve
this?
> --
> TIA
> Altman
>
>|||I mean that I have a trigger and inside of that trigger I am calling a
Delete from table ..... I need to know if the delete that I am calling
inside the trigger goes through.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> What do you mean by fail? If the delete fails with an error message, like
> a foreign key constraint violation, the trigger will not be fired. If you
> mean modify zero rows, check @.@.ROWCOUNT the very first thing you do in the
> triggers, or check the number of rows in the deleted table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||It depends on what you mean by "goes though". If it generates and error, you
can catch that error in
your trigger code (@.@.error), just like you do in any TSQL code. And you can
get the number of rows
modified using @.@.ROWCOUNT. I suggest you check out the error handling articles at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:O4jWsSm2FHA.2624@.TK2MSFTNGP09.phx.
gbl...
>I mean that I have a trigger and inside of that trigger I am calling a Dele
te from table ..... I
>need to know if the delete that I am calling inside the trigger goes throug
h.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>
delete on another table. I need to know whether this delete fails or not.
How can I achieve this?
TIA
Altmancheck the rowcount in the trigger
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||What do you mean by fail? If the delete fails with an error message, like a
foreign key constraint
violation, the trigger will not be fired. If you mean modify zero rows, chec
k @.@.ROWCOUNT the very
first thing you do in the triggers, or check the number of rows in the delet
ed table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.
gbl...
>I am new to SQL Server and I am trying to write a trigger where I am doing
a delete on another
>table. I need to know whether this delete fails or not. How can I achieve
this?
> --
> TIA
> Altman
>
>|||I mean that I have a trigger and inside of that trigger I am calling a
Delete from table ..... I need to know if the delete that I am calling
inside the trigger goes through.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> What do you mean by fail? If the delete fails with an error message, like
> a foreign key constraint violation, the trigger will not be fired. If you
> mean modify zero rows, check @.@.ROWCOUNT the very first thing you do in the
> triggers, or check the number of rows in the deleted table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||It depends on what you mean by "goes though". If it generates and error, you
can catch that error in
your trigger code (@.@.error), just like you do in any TSQL code. And you can
get the number of rows
modified using @.@.ROWCOUNT. I suggest you check out the error handling articles at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:O4jWsSm2FHA.2624@.TK2MSFTNGP09.phx.
gbl...
>I mean that I have a trigger and inside of that trigger I am calling a Dele
te from table ..... I
>need to know if the delete that I am calling inside the trigger goes throug
h.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>
Tuesday, March 20, 2012
Check for table and return true or false
How can I write a stored procedure to return if a table exist or not?
I put:
CREATE procedure sp_BA_ReportExist
(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)
AS
DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null RETURN "+@.ISYES+" ELSE RETURN "+@.ISNO+" "
EXEC(@.SQL)
GO
I ran it with: sp_BA_ReportExist '1','0'
but I get:
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
How can I make this work?
Thanks!
KenFigured it out... this works:
CREATE procedure sp_BA_ReportExist
(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)
AS
DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null PRINT "+@.ISYES+" ELSE PRINT "+@.ISNO+" "
EXEC(@.SQL)
GO
As always I find the answer right after I post!|||you could also use:
declare @.TableName sysname
set @.TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') = 1
print "+@.ISYES+"
else
print "+@.ISNO+"|||I have another problem now...
How do I get the return value?|||do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?|||A resultset will work.
Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.
Thanks so much for any light you can shed on this!
Ken|||try:
create procedure sp_BA_ReportExist(
@.ISYES VARCHAR (10)
, @.ISNO VARCHAR (10))
AS
if (object_id('BA_REPORT_MASTER') is not null)
select @.ISYES as Answer
else
select @.ISNO as Answer
return 0
GO
exec sp_BA_ReportExist 'Yes', 'No'|||Too Cool! Thank you so much!
I was kinda close, but didn't have it quite right!
Thanks for your help!
Ken|||or:
create procedure sp_TableExists(
@.TableName sysname
, @.ISYES VARCHAR (10) = 'Yes'
, @.ISNO VARCHAR (10) = 'No')
AS
select case OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') when 1 then @.ISYES else @.ISNO end as Answer
return 0
GO
exec sp_TableExists 'sysobjects','Yes', 'No'
or just
exec sp_TableExists 'sysobjects'
I put:
CREATE procedure sp_BA_ReportExist
(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)
AS
DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null RETURN "+@.ISYES+" ELSE RETURN "+@.ISNO+" "
EXEC(@.SQL)
GO
I ran it with: sp_BA_ReportExist '1','0'
but I get:
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
How can I make this work?
Thanks!
KenFigured it out... this works:
CREATE procedure sp_BA_ReportExist
(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)
AS
DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null PRINT "+@.ISYES+" ELSE PRINT "+@.ISNO+" "
EXEC(@.SQL)
GO
As always I find the answer right after I post!|||you could also use:
declare @.TableName sysname
set @.TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') = 1
print "+@.ISYES+"
else
print "+@.ISNO+"|||I have another problem now...
How do I get the return value?|||do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?|||A resultset will work.
Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.
Thanks so much for any light you can shed on this!
Ken|||try:
create procedure sp_BA_ReportExist(
@.ISYES VARCHAR (10)
, @.ISNO VARCHAR (10))
AS
if (object_id('BA_REPORT_MASTER') is not null)
select @.ISYES as Answer
else
select @.ISNO as Answer
return 0
GO
exec sp_BA_ReportExist 'Yes', 'No'|||Too Cool! Thank you so much!
I was kinda close, but didn't have it quite right!
Thanks for your help!
Ken|||or:
create procedure sp_TableExists(
@.TableName sysname
, @.ISYES VARCHAR (10) = 'Yes'
, @.ISNO VARCHAR (10) = 'No')
AS
select case OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') when 1 then @.ISYES else @.ISNO end as Answer
return 0
GO
exec sp_TableExists 'sysobjects','Yes', 'No'
or just
exec sp_TableExists 'sysobjects'
Sunday, March 11, 2012
Check database healthiness on a daily basis?
How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each
one in a T-SQL script and have it run every day. Is there a better way to
do that?
Thanks in advance for any advices.
BingHi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing|||Thanks so much for your instance response, Hari. Not just for this one. Yo
u have answered a lot of my questions I posted previously. They are all ver
y helpful. I really appreciate your knowledgement and your kindness of will
ing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:
> Hi,
> Yes, that will be a better option to check and confirm that your database
is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better w
ay
> to do that?
>
>|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
>
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:
> Bing,
> Something else to keep in mind if you have some large db's or ones that ar
e
> 24 x 7. You can restore a full backup on another machine and run the DBCC
's
> there. If it is corrupted on the primary it will be corrupted there as we
ll
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better[vbcol=seagreen]
> way
>|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'[vbcol=seagreen]
> better
>
We have about 50 databases. I was intended to write 'dbcc checkdb' for each
one in a T-SQL script and have it run every day. Is there a better way to
do that?
Thanks in advance for any advices.
BingHi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing|||Thanks so much for your instance response, Hari. Not just for this one. Yo
u have answered a lot of my questions I posted previously. They are all ver
y helpful. I really appreciate your knowledgement and your kindness of will
ing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:
> Hi,
> Yes, that will be a better option to check and confirm that your database
is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better w
ay
> to do that?
>
>|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
>
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:
> Bing,
> Something else to keep in mind if you have some large db's or ones that ar
e
> 24 x 7. You can restore a full backup on another machine and run the DBCC
's
> there. If it is corrupted on the primary it will be corrupted there as we
ll
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better[vbcol=seagreen]
> way
>|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'[vbcol=seagreen]
> better
>
Check database healthiness on a daily basis?
How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each one in a T-SQL script and have it run every day. Is there a better way to do that?
Thanks in advance for any advices.
Bing
Hi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing
|||Thanks so much for your instance response, Hari. Not just for this one. You have answered a lot of my questions I posted previously. They are all very helpful. I really appreciate your knowledgement and your kindness of willing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:
> Hi,
> Yes, that will be a better option to check and confirm that your database is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better way
> to do that?
>
>
|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]
|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:
> Bing,
> Something else to keep in mind if you have some large db's or ones that are
> 24 x 7. You can restore a full backup on another machine and run the DBCC's
> there. If it is corrupted on the primary it will be corrupted there as well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>
|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well[vbcol=seagreen]
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better
> way
>
|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to[vbcol=seagreen]
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'
> better
>
We have about 50 databases. I was intended to write 'dbcc checkdb' for each one in a T-SQL script and have it run every day. Is there a better way to do that?
Thanks in advance for any advices.
Bing
Hi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing
|||Thanks so much for your instance response, Hari. Not just for this one. You have answered a lot of my questions I posted previously. They are all very helpful. I really appreciate your knowledgement and your kindness of willing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:
> Hi,
> Yes, that will be a better option to check and confirm that your database is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better way
> to do that?
>
>
|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]
|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:
> Bing,
> Something else to keep in mind if you have some large db's or ones that are
> 24 x 7. You can restore a full backup on another machine and run the DBCC's
> there. If it is corrupted on the primary it will be corrupted there as well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>
|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well[vbcol=seagreen]
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better
> way
>
|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to[vbcol=seagreen]
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'
> better
>
check constraint or rule for control characters
I'm trying to control the accidental entry of special characters (carriage
return) from getting into my data. I thought I could write a check
constraint like what follows
[name] <> '%' + char(13) + '%'
but this doesn't work. Tried several permutations but the carriage return
is always accepted when I enter the data through an access database table
view (control + enter).
If I can get the expression working I think the best way to implement would
be either a user defined datatype or a rule bound to the column.
Has anyone done this sort of thing, or know how?
Thanks
David LHow about
CharIndex(Char(13), [Name], 1) = 0
Or
[Name] Not Like '%' + Char(13) + '%'
Thomas
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
> I'm trying to control the accidental entry of special characters (carriage
> return) from getting into my data. I thought I could write a check constr
aint
> like what follows
> [name] <> '%' + char(13) + '%'
> but this doesn't work. Tried several permutations but the carriage return
is
> always accepted when I enter the data through an access database table vie
w
> (control + enter).
> If I can get the expression working I think the best way to implement woul
d be
> either a user defined datatype or a rule bound to the column.
> Has anyone done this sort of thing, or know how?
> Thanks
>
> --
> David L
>|||Ok, the second works great thanks
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
> How about
> CharIndex(Char(13), [Name], 1) = 0
> Or
> [Name] Not Like '%' + Char(13) + '%'
>
> Thomas
>
> "DavinciCoder" <dal@.rlpi.com> wrote in message
> news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||David
You may want to check CHAR(10) as well.
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:%23X6if6uaFHA.2884@.tk2msftngp13.phx.gbl...
> Ok, the second works great thanks
>
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
>
return) from getting into my data. I thought I could write a check
constraint like what follows
[name] <> '%' + char(13) + '%'
but this doesn't work. Tried several permutations but the carriage return
is always accepted when I enter the data through an access database table
view (control + enter).
If I can get the expression working I think the best way to implement would
be either a user defined datatype or a rule bound to the column.
Has anyone done this sort of thing, or know how?
Thanks
David LHow about
CharIndex(Char(13), [Name], 1) = 0
Or
[Name] Not Like '%' + Char(13) + '%'
Thomas
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
> I'm trying to control the accidental entry of special characters (carriage
> return) from getting into my data. I thought I could write a check constr
aint
> like what follows
> [name] <> '%' + char(13) + '%'
> but this doesn't work. Tried several permutations but the carriage return
is
> always accepted when I enter the data through an access database table vie
w
> (control + enter).
> If I can get the expression working I think the best way to implement woul
d be
> either a user defined datatype or a rule bound to the column.
> Has anyone done this sort of thing, or know how?
> Thanks
>
> --
> David L
>|||Ok, the second works great thanks
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
> How about
> CharIndex(Char(13), [Name], 1) = 0
> Or
> [Name] Not Like '%' + Char(13) + '%'
>
> Thomas
>
> "DavinciCoder" <dal@.rlpi.com> wrote in message
> news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||David
You may want to check CHAR(10) as well.
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:%23X6if6uaFHA.2884@.tk2msftngp13.phx.gbl...
> Ok, the second works great thanks
>
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
>
Wednesday, March 7, 2012
Check 3 occurrences of double characters.
Hi all,
I need to write some sort of statement to identify which numbers from a list fall into the following pattern:
% TwoIdenticalNumbers % TwoIdenticalNumbers % TwoIdenticalNumbers %
for example:
08812355677
I thought I would be able to use a LIKE statement but I'm not sure how to write it so that double characters are used rather than single. For example if I wanted to check three numbers appear within the string I could do the following:
SELECT *
FROM Table
WHERE Number LIKE '%[0-9]%[0-9]%[0-9]%'
I think the easiest way to check the doubles would be to represent them as a string, so I want to replace each of the [0-9] above with something like the following:
['00' OR '11' OR '22' OR '33' OR '44' OR '55' OR '66' OR '66' OR '77' OR '88' OR '99']
How could I write this using proper SQL code?
Any help would be much appreciated.
Thanks very much,
Will
Thre is no predefined expressions available,
Following approach is one of the way to achive this,
Code Snippet
Create Table #data (
[Numbers] Varchar(100)
);
Insert Into #data Values('1242432');
Insert Into #data Values('242423423');
Insert Into #data Values('2332232');
Insert Into #data Values('828289');
Insert Into #data Values('99887766');
Insert Into #data Values('92829299');
select
numbers
from #data
cross join
(
select '00' n
union all
select '11'
union all
select '22'
union all
select '33'
union all
select '44'
union all
select '55'
union all
select '66'
union all
select '77'
union all
select '88'
union all
select '99' ) as d
group by numbers having sum(case when patindex('%'+ n + '%',numbers) <> 0 Then 1 Else 0 End) >= 3
|||:-) Looks oofy, but works
SELECT * FROM sysobjects
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'
|||
The idea is good, but the following query might fit the asked requirement,(3 occurrence of …)
Code Snippet
SELECT Id FROM sysobjects
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%X%X%'
|||
Very close. At least three instances are needed.
'11' -- does not qualify
'1122' -- does not qualify
'112233' -- bingo
...
SELECT
*
FROM
(select '11' as ID) as t
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'
AMB
|||Talking about ugly. Try:
-- thanks to Manni for the sample data
select
numbers
from
#Data
where
(len(numbers) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(numbers, '00', ''), '11', ''), '22', ''), '33', ''), '44', ''), '55', ''), '66', ''), '77', ''), '88', ''), '99', ''))) / 2 >= 3
AMB
|||Thanks for all the help, this has saved me a lot of time trying to figure this one out.Chasing my tail with this design
I've never been great at DB Design, I try though.
Here is my situation.
We manufacture hardware devices(startup Co.). I write the firmware for the
devices and generally all other software issues.
As we are growing, we were having more and more customers wanting
"customized" versions of the firmware, being a startup, we aren't about to
say "no" yet.
Rather than edit the source everytime someone needs a custom change, I
decided to write a Firmware Editor that would take the data entered for the
customer, store it in the database and when need, generate source code
header files to be used in batch builds when we release new version of the
firmware.
The device performs a set of "tasks" and each task is broken down into
several sets of "operations".
There are 3 models of our device, each performing exactly the same "tasks"
and "operations" but with different "power output" - in the future we
foresee users wanting to further customize the firmware on a per-model
level.
So for example, customer A might want Model1 to run task # 4 for 30 minutes
Model2 to run task #4 for 20 minutes
Model3 to run task #4 for 45 minutes.
The device is not interactively programmable by the users as 90% of our
customers "like it how it is"
I have roughed out the first database design pass, it's like this:
[Tbl_Customer]
PK CustomerID
CustomerName
// configs allow a customer to have "sets" of firmware for their different
needs. The example above w/ "customer A" and "Model1/2/3" was an example of
a "Config"
[Tbl_CustomerConfig]
PK ConfigID
CustomerID
ConfigName
[Tbl_DeviceSettings]
PK DeviceSettingID
FK ConfigID
FK DeviceID
BootScreenText
BaseFreq
[Tbl_Devices]
PK DeviceID
DeviceName
[Tbl_Tasks]
PK TaskID
FK DeviceSettingsID
TaskName
TaskDuration
TaskOppCode
// each task is comprised of many "Task Operations" - customers are wanting
us to change these as well
[Tbl_TaskOperations]
PK TaskOperationID
FK TaskID
StartFrequency
EndFrequency
StartAddress
Now, the problem is... I want to have ALL customers in the system, even the
ones that don't want changes(yet) so that if/when they want changes, we can
deliver the new firmware fast and not worry about entering them into the
system, etc. There are other advantages as well.
Based on my current schema, I would be storing data for the 90% of customers
that DON'T want to customize their data, this seems so wasteful. And even
if they did want to edit just one of the 30 available tasks, I would be
storing all those duplicates.
Another problem is that If I need to make a change to non-customized task, I
would need to make it for ever device, in every config for each customer.
Stupid.
I though about making a "base" customer, "base" config, "base" tasks with
"base" operations. All customers would have FKs to these "base" records
until they decide they want something custom, then at that point I edit the
data and it creates a new record for only the data that has changed. Does
that make sense?
I hope someone understands what I'm after, it's hard to explain.
Thanks for reading this far!
Steve"Steve" <sss@.sss.com> wrote in message
news:OYSuOHHyFHA.1132@.TK2MSFTNGP10.phx.gbl...
> I've never been great at DB Design, I try though.
> Here is my situation.
> We manufacture hardware devices(startup Co.). I write the firmware for
the
> devices and generally all other software issues.
> As we are growing, we were having more and more customers wanting
> "customized" versions of the firmware, being a startup, we aren't about to
> say "no" yet.
> Rather than edit the source everytime someone needs a custom change, I
> decided to write a Firmware Editor that would take the data entered for
the
> customer, store it in the database and when need, generate source code
> header files to be used in batch builds when we release new version of the
> firmware.
> The device performs a set of "tasks" and each task is broken down into
> several sets of "operations".
> There are 3 models of our device, each performing exactly the same "tasks"
> and "operations" but with different "power output" - in the future we
> foresee users wanting to further customize the firmware on a per-model
> level.
> So for example, customer A might want Model1 to run task # 4 for 30
minutes
> Model2 to run task #4 for 20 minutes
> Model3 to run task #4 for 45 minutes.
>
> The device is not interactively programmable by the users as 90% of our
> customers "like it how it is"
>
> I have roughed out the first database design pass, it's like this:
> [Tbl_Customer]
> PK CustomerID
> CustomerName
>
> // configs allow a customer to have "sets" of firmware for their
different
> needs. The example above w/ "customer A" and "Model1/2/3" was an example
of
> a "Config"
> [Tbl_CustomerConfig]
> PK ConfigID
> CustomerID
> ConfigName
> [Tbl_DeviceSettings]
> PK DeviceSettingID
> FK ConfigID
> FK DeviceID
> BootScreenText
> BaseFreq
> [Tbl_Devices]
> PK DeviceID
> DeviceName
> [Tbl_Tasks]
> PK TaskID
> FK DeviceSettingsID
> TaskName
> TaskDuration
> TaskOppCode
> // each task is comprised of many "Task Operations" - customers are
wanting
> us to change these as well
> [Tbl_TaskOperations]
> PK TaskOperationID
> FK TaskID
> StartFrequency
> EndFrequency
> StartAddress
>
>
> Now, the problem is... I want to have ALL customers in the system, even
the
> ones that don't want changes(yet) so that if/when they want changes, we
can
> deliver the new firmware fast and not worry about entering them into the
> system, etc. There are other advantages as well.
> Based on my current schema, I would be storing data for the 90% of
customers
> that DON'T want to customize their data, this seems so wasteful. And even
> if they did want to edit just one of the 30 available tasks, I would be
> storing all those duplicates.
> Another problem is that If I need to make a change to non-customized task,
I
> would need to make it for ever device, in every config for each customer.
> Stupid.
> I though about making a "base" customer, "base" config, "base" tasks with
> "base" operations. All customers would have FKs to these "base" records
> until they decide they want something custom, then at that point I edit
the
> data and it creates a new record for only the data that has changed. Does
> that make sense?
> I hope someone understands what I'm after, it's hard to explain.
> Thanks for reading this far!
> Steve
>
>
Another thing that I thought of was to have a duplicate Tbl_Task that would
hold the unchanged, non-custom data, then another table that had all the
CustomTasks, but that doesn't seem like a good solution, seems hacky...|||How about having a discrete set of customizations (the operation, task, etc
collection/unit/whatever) and associating one (or many) with each customer.
That way one customized task could serve multiple customers, starting with a
default configuration for that 90%:
CREATE TABLE dbo.Customizations
(
id int PK
, name char unique
, ... attributes
)
CREATE TABLE dbo.Customers
(
id int PK
, name char unique
, customization_id int (FK to Customizations) -- drop this column if you go
1-many
)
For a 1-many relationship create a cross-reference table:
CREATE TABLE dbo.CustomerCustomizations
(
customer_id int (FK to Customers.id)
, customization_id int (FK to Customizations.id)
, PK(customer_id, customization_id)
)
"Steve" wrote:
> "Steve" <sss@.sss.com> wrote in message
> news:OYSuOHHyFHA.1132@.TK2MSFTNGP10.phx.gbl...
> the
> the
> minutes
> different
> of
> wanting
> the
> can
> customers
> I
> the
> Another thing that I thought of was to have a duplicate Tbl_Task that woul
d
> hold the unchanged, non-custom data, then another table that had all the
> CustomTasks, but that doesn't seem like a good solution, seems hacky...
>
>|||Hello KH,
Thank you for your post. I have been messing with the design this morning
and have begun doing something like you suggested.
I'm looking forward to having everything work ;)
Thanks again,
Steve
"KH" <KH@.discussions.microsoft.com> wrote in message
news:DB5EBD4B-615F-414F-8179-DA895098CD2B@.microsoft.com...
> How about having a discrete set of customizations (the operation, task,
etc
> collection/unit/whatever) and associating one (or many) with each
customer.
> That way one customized task could serve multiple customers, starting with
a
> default configuration for that 90%:
> CREATE TABLE dbo.Customizations
> (
> id int PK
> , name char unique
> , ... attributes
> )
> CREATE TABLE dbo.Customers
> (
> id int PK
> , name char unique
> , customization_id int (FK to Customizations) -- drop this column if you
go
> 1-many
> )
>
> For a 1-many relationship create a cross-reference table:
> CREATE TABLE dbo.CustomerCustomizations
> (
> customer_id int (FK to Customers.id)
> , customization_id int (FK to Customizations.id)
> , PK(customer_id, customization_id)
> )
>
> "Steve" wrote:
>
for
about to
for
the
"tasks"
our
example
even
we
the
even
be
task,
customer.
with
records
edit
Does
would
Here is my situation.
We manufacture hardware devices(startup Co.). I write the firmware for the
devices and generally all other software issues.
As we are growing, we were having more and more customers wanting
"customized" versions of the firmware, being a startup, we aren't about to
say "no" yet.
Rather than edit the source everytime someone needs a custom change, I
decided to write a Firmware Editor that would take the data entered for the
customer, store it in the database and when need, generate source code
header files to be used in batch builds when we release new version of the
firmware.
The device performs a set of "tasks" and each task is broken down into
several sets of "operations".
There are 3 models of our device, each performing exactly the same "tasks"
and "operations" but with different "power output" - in the future we
foresee users wanting to further customize the firmware on a per-model
level.
So for example, customer A might want Model1 to run task # 4 for 30 minutes
Model2 to run task #4 for 20 minutes
Model3 to run task #4 for 45 minutes.
The device is not interactively programmable by the users as 90% of our
customers "like it how it is"
I have roughed out the first database design pass, it's like this:
[Tbl_Customer]
PK CustomerID
CustomerName
// configs allow a customer to have "sets" of firmware for their different
needs. The example above w/ "customer A" and "Model1/2/3" was an example of
a "Config"
[Tbl_CustomerConfig]
PK ConfigID
CustomerID
ConfigName
[Tbl_DeviceSettings]
PK DeviceSettingID
FK ConfigID
FK DeviceID
BootScreenText
BaseFreq
[Tbl_Devices]
PK DeviceID
DeviceName
[Tbl_Tasks]
PK TaskID
FK DeviceSettingsID
TaskName
TaskDuration
TaskOppCode
// each task is comprised of many "Task Operations" - customers are wanting
us to change these as well
[Tbl_TaskOperations]
PK TaskOperationID
FK TaskID
StartFrequency
EndFrequency
StartAddress
Now, the problem is... I want to have ALL customers in the system, even the
ones that don't want changes(yet) so that if/when they want changes, we can
deliver the new firmware fast and not worry about entering them into the
system, etc. There are other advantages as well.
Based on my current schema, I would be storing data for the 90% of customers
that DON'T want to customize their data, this seems so wasteful. And even
if they did want to edit just one of the 30 available tasks, I would be
storing all those duplicates.
Another problem is that If I need to make a change to non-customized task, I
would need to make it for ever device, in every config for each customer.
Stupid.
I though about making a "base" customer, "base" config, "base" tasks with
"base" operations. All customers would have FKs to these "base" records
until they decide they want something custom, then at that point I edit the
data and it creates a new record for only the data that has changed. Does
that make sense?
I hope someone understands what I'm after, it's hard to explain.
Thanks for reading this far!
Steve"Steve" <sss@.sss.com> wrote in message
news:OYSuOHHyFHA.1132@.TK2MSFTNGP10.phx.gbl...
> I've never been great at DB Design, I try though.
> Here is my situation.
> We manufacture hardware devices(startup Co.). I write the firmware for
the
> devices and generally all other software issues.
> As we are growing, we were having more and more customers wanting
> "customized" versions of the firmware, being a startup, we aren't about to
> say "no" yet.
> Rather than edit the source everytime someone needs a custom change, I
> decided to write a Firmware Editor that would take the data entered for
the
> customer, store it in the database and when need, generate source code
> header files to be used in batch builds when we release new version of the
> firmware.
> The device performs a set of "tasks" and each task is broken down into
> several sets of "operations".
> There are 3 models of our device, each performing exactly the same "tasks"
> and "operations" but with different "power output" - in the future we
> foresee users wanting to further customize the firmware on a per-model
> level.
> So for example, customer A might want Model1 to run task # 4 for 30
minutes
> Model2 to run task #4 for 20 minutes
> Model3 to run task #4 for 45 minutes.
>
> The device is not interactively programmable by the users as 90% of our
> customers "like it how it is"
>
> I have roughed out the first database design pass, it's like this:
> [Tbl_Customer]
> PK CustomerID
> CustomerName
>
> // configs allow a customer to have "sets" of firmware for their
different
> needs. The example above w/ "customer A" and "Model1/2/3" was an example
of
> a "Config"
> [Tbl_CustomerConfig]
> PK ConfigID
> CustomerID
> ConfigName
> [Tbl_DeviceSettings]
> PK DeviceSettingID
> FK ConfigID
> FK DeviceID
> BootScreenText
> BaseFreq
> [Tbl_Devices]
> PK DeviceID
> DeviceName
> [Tbl_Tasks]
> PK TaskID
> FK DeviceSettingsID
> TaskName
> TaskDuration
> TaskOppCode
> // each task is comprised of many "Task Operations" - customers are
wanting
> us to change these as well
> [Tbl_TaskOperations]
> PK TaskOperationID
> FK TaskID
> StartFrequency
> EndFrequency
> StartAddress
>
>
> Now, the problem is... I want to have ALL customers in the system, even
the
> ones that don't want changes(yet) so that if/when they want changes, we
can
> deliver the new firmware fast and not worry about entering them into the
> system, etc. There are other advantages as well.
> Based on my current schema, I would be storing data for the 90% of
customers
> that DON'T want to customize their data, this seems so wasteful. And even
> if they did want to edit just one of the 30 available tasks, I would be
> storing all those duplicates.
> Another problem is that If I need to make a change to non-customized task,
I
> would need to make it for ever device, in every config for each customer.
> Stupid.
> I though about making a "base" customer, "base" config, "base" tasks with
> "base" operations. All customers would have FKs to these "base" records
> until they decide they want something custom, then at that point I edit
the
> data and it creates a new record for only the data that has changed. Does
> that make sense?
> I hope someone understands what I'm after, it's hard to explain.
> Thanks for reading this far!
> Steve
>
>
Another thing that I thought of was to have a duplicate Tbl_Task that would
hold the unchanged, non-custom data, then another table that had all the
CustomTasks, but that doesn't seem like a good solution, seems hacky...|||How about having a discrete set of customizations (the operation, task, etc
collection/unit/whatever) and associating one (or many) with each customer.
That way one customized task could serve multiple customers, starting with a
default configuration for that 90%:
CREATE TABLE dbo.Customizations
(
id int PK
, name char unique
, ... attributes
)
CREATE TABLE dbo.Customers
(
id int PK
, name char unique
, customization_id int (FK to Customizations) -- drop this column if you go
1-many
)
For a 1-many relationship create a cross-reference table:
CREATE TABLE dbo.CustomerCustomizations
(
customer_id int (FK to Customers.id)
, customization_id int (FK to Customizations.id)
, PK(customer_id, customization_id)
)
"Steve" wrote:
> "Steve" <sss@.sss.com> wrote in message
> news:OYSuOHHyFHA.1132@.TK2MSFTNGP10.phx.gbl...
> the
> the
> minutes
> different
> of
> wanting
> the
> can
> customers
> I
> the
> Another thing that I thought of was to have a duplicate Tbl_Task that woul
d
> hold the unchanged, non-custom data, then another table that had all the
> CustomTasks, but that doesn't seem like a good solution, seems hacky...
>
>|||Hello KH,
Thank you for your post. I have been messing with the design this morning
and have begun doing something like you suggested.
I'm looking forward to having everything work ;)
Thanks again,
Steve
"KH" <KH@.discussions.microsoft.com> wrote in message
news:DB5EBD4B-615F-414F-8179-DA895098CD2B@.microsoft.com...
> How about having a discrete set of customizations (the operation, task,
etc
> collection/unit/whatever) and associating one (or many) with each
customer.
> That way one customized task could serve multiple customers, starting with
a
> default configuration for that 90%:
> CREATE TABLE dbo.Customizations
> (
> id int PK
> , name char unique
> , ... attributes
> )
> CREATE TABLE dbo.Customers
> (
> id int PK
> , name char unique
> , customization_id int (FK to Customizations) -- drop this column if you
go
> 1-many
> )
>
> For a 1-many relationship create a cross-reference table:
> CREATE TABLE dbo.CustomerCustomizations
> (
> customer_id int (FK to Customers.id)
> , customization_id int (FK to Customizations.id)
> , PK(customer_id, customization_id)
> )
>
> "Steve" wrote:
>
for
about to
for
the
"tasks"
our
example
even
we
the
even
be
task,
customer.
with
records
edit
Does
would
Sunday, February 19, 2012
Chart Filters
HI,
Does anybody know how to write a chart filter that will return only the last row in the record set and conversely return all rows except for the last in the data set (for a separate chart)
My Report consists of a dataset that has the total in the last row. I want to create a chart with only the total row and another chart on the same page using the same data set but excluding the total row.
Cheers
KevinIf there is anything special about the row it would be straight forward as a
chart filter and a table filter to separate the rows. If the only
distinguishing feature of the row is that it is the last row then I can not
think of a way in RS 2000 to do this. We are considering allowing
aggregates of aggregates, which would allow you to filter on the max
rownumber.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> HI,
> Does anybody know how to write a chart filter that will return only the
last row in the record set and conversely return all rows except for the
last in the data set (for a separate chart)
> My Report consists of a dataset that has the total in the last row. I want
to create a chart with only the total row and another chart on the same
page using the same data set but excluding the total row.
> Cheers
> Kevin|||Jason,
There is nothing special about the row except that it's the last. However I've managed to created a chart filter that returns the last row by using the Bottom N operator.
expression operator value
=Fields!RH0_Product.Value BottomN =1
However do you know how to express a filter that defines where the rows is NOT = to the BottomN 1
If I can get this then I've solved my problem...
Much appreciated.
Kevin
"Jason Carlson [MSFT]" wrote:
> If there is anything special about the row it would be straight forward as a
> chart filter and a table filter to separate the rows. If the only
> distinguishing feature of the row is that it is the last row then I can not
> think of a way in RS 2000 to do this. We are considering allowing
> aggregates of aggregates, which would allow you to filter on the max
> rownumber.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > HI,
> > Does anybody know how to write a chart filter that will return only the
> last row in the record set and conversely return all rows except for the
> last in the data set (for a separate chart)
> >
> > My Report consists of a dataset that has the total in the last row. I want
> to create a chart with only the total row and another chart on the same
> page using the same data set but excluding the total row.
> >
> > Cheers
> > Kevin
>
>|||Please see my response to your "NOT BottomN" thread started on 06/29.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A6A1CBD8-F987-472C-B507-E0AA21FD3A4C@.microsoft.com...
> Jason,
> There is nothing special about the row except that it's the last. However
I've managed to created a chart filter that returns the last row by using
the Bottom N operator.
> expression operator value
> =Fields!RH0_Product.Value BottomN =1
> However do you know how to express a filter that defines where the rows
is NOT = to the BottomN 1
> If I can get this then I've solved my problem...
> Much appreciated.
> Kevin
>
> "Jason Carlson [MSFT]" wrote:
> > If there is anything special about the row it would be straight forward
as a
> > chart filter and a table filter to separate the rows. If the only
> > distinguishing feature of the row is that it is the last row then I can
not
> > think of a way in RS 2000 to do this. We are considering allowing
> > aggregates of aggregates, which would allow you to filter on the max
> > rownumber.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > > HI,
> > > Does anybody know how to write a chart filter that will return only
the
> > last row in the record set and conversely return all rows except for the
> > last in the data set (for a separate chart)
> > >
> > > My Report consists of a dataset that has the total in the last row. I
want
> > to create a chart with only the total row and another chart on the same
> > page using the same data set but excluding the total row.
> > >
> > > Cheers
> > > Kevin
> >
> >
> >
Does anybody know how to write a chart filter that will return only the last row in the record set and conversely return all rows except for the last in the data set (for a separate chart)
My Report consists of a dataset that has the total in the last row. I want to create a chart with only the total row and another chart on the same page using the same data set but excluding the total row.
Cheers
KevinIf there is anything special about the row it would be straight forward as a
chart filter and a table filter to separate the rows. If the only
distinguishing feature of the row is that it is the last row then I can not
think of a way in RS 2000 to do this. We are considering allowing
aggregates of aggregates, which would allow you to filter on the max
rownumber.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> HI,
> Does anybody know how to write a chart filter that will return only the
last row in the record set and conversely return all rows except for the
last in the data set (for a separate chart)
> My Report consists of a dataset that has the total in the last row. I want
to create a chart with only the total row and another chart on the same
page using the same data set but excluding the total row.
> Cheers
> Kevin|||Jason,
There is nothing special about the row except that it's the last. However I've managed to created a chart filter that returns the last row by using the Bottom N operator.
expression operator value
=Fields!RH0_Product.Value BottomN =1
However do you know how to express a filter that defines where the rows is NOT = to the BottomN 1
If I can get this then I've solved my problem...
Much appreciated.
Kevin
"Jason Carlson [MSFT]" wrote:
> If there is anything special about the row it would be straight forward as a
> chart filter and a table filter to separate the rows. If the only
> distinguishing feature of the row is that it is the last row then I can not
> think of a way in RS 2000 to do this. We are considering allowing
> aggregates of aggregates, which would allow you to filter on the max
> rownumber.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > HI,
> > Does anybody know how to write a chart filter that will return only the
> last row in the record set and conversely return all rows except for the
> last in the data set (for a separate chart)
> >
> > My Report consists of a dataset that has the total in the last row. I want
> to create a chart with only the total row and another chart on the same
> page using the same data set but excluding the total row.
> >
> > Cheers
> > Kevin
>
>|||Please see my response to your "NOT BottomN" thread started on 06/29.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A6A1CBD8-F987-472C-B507-E0AA21FD3A4C@.microsoft.com...
> Jason,
> There is nothing special about the row except that it's the last. However
I've managed to created a chart filter that returns the last row by using
the Bottom N operator.
> expression operator value
> =Fields!RH0_Product.Value BottomN =1
> However do you know how to express a filter that defines where the rows
is NOT = to the BottomN 1
> If I can get this then I've solved my problem...
> Much appreciated.
> Kevin
>
> "Jason Carlson [MSFT]" wrote:
> > If there is anything special about the row it would be straight forward
as a
> > chart filter and a table filter to separate the rows. If the only
> > distinguishing feature of the row is that it is the last row then I can
not
> > think of a way in RS 2000 to do this. We are considering allowing
> > aggregates of aggregates, which would allow you to filter on the max
> > rownumber.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > > HI,
> > > Does anybody know how to write a chart filter that will return only
the
> > last row in the record set and conversely return all rows except for the
> > last in the data set (for a separate chart)
> > >
> > > My Report consists of a dataset that has the total in the last row. I
want
> > to create a chart with only the total row and another chart on the same
> > page using the same data set but excluding the total row.
> > >
> > > Cheers
> > > Kevin
> >
> >
> >
Tuesday, February 14, 2012
Character to date conversion
I am trying to write and insert statement that takes up a date value, such as,
Insert into tablename(date_column) values (date('some date', format));
I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?
Insert into tablename(date_column) values (date('some date', format));
I however do not know how exactly to convert a character string to transact sql date. can someone please lead me in correct direction? Some documentation could also help.
Thanks
--Shilpa
No special functions needed. Just insert it as text:
CREATE TABLE test
(
dateValue datetime
)
go
INSERT INTO test
VALUES ('2006-01-01T00:00:00')
go
As for format of datetime values, there are many different ways to format a date, but really only a couple of good ways. Look up "Date Data Types" in books online. It explains it really well.
|||I am new to MS domain. Couldn't get to Books online.I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?
Thanks
S
|||INSERT INTO test
VALUES ('2006-01-01T00:00:00')
Sorry about the previous post. Your solution works without the T before time insertion.
Thanks,
S
|||
There is a copy of books online (not unsurprisingly) online:
http://msdn2.microsoft.com/en-us/ms130214(sql.90).aspx
You can also download it online. They update it quarterly, and this download is from April:
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en
Sunday, February 12, 2012
Char compare to String
hi
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:
> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:
> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>
Subscribe to:
Posts (Atom)