Is there SQL code that will report the name of the Windows account that the
SQL Server service is using? I'd be doing this using a login in the sysadmi
n
role.
I've searched BOL, the web, and newsgroups. But I haven't found a way.
Thanks
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comJon
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:17635F3D-6CF1-4ECA-941D-DAB9652D81D7@.microsoft.com...
> Is there SQL code that will report the name of the Windows account that
> the
> SQL Server service is using? I'd be doing this using a login in the
> sysadmin
> role.
> I've searched BOL, the web, and newsgroups. But I haven't found a way.
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.comsql
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Determine if database objects are being accessed
Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:
> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:
>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:
> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
> > A clunky, but effective way to track if someone is using the view or
> > trigger is:
> >
> > 1.) VIEW - add a trigger on the view to write to a table everytime the
> > view is accessed
> >
> > 2.) TRIGGER - have the trigger write to a table everytime the trigger
> > fires, as part of the trigger
> >
> > You can include timestamp and system_user data in your table
> >
> >
> > Adi wrote:
> > > I think that those queries might do the job:
> > >
> > > --check stored procedures
> > > select specific_name
> > > from INFORMATION_SCHEMA.ROUTINES
> > > where ROUTINE_DEFINITION like '%ObjectName%'
> > >
> > > --check views
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.VIEWS
> > > where VIEW_DEFINITION like '%ObjectName%'
> > >
> > > The queries will help only for objects in the database, but if you
> > > have an application that uses direct SQL statements, you'll get runtime
> > > errors. You can try and use the profiler to see if external
> > > applications use the old names in direct statements. You can also try
> > > to create a synonym with the old name that will point to the new name
> > > (didn't try it myself, but I think that this should work)
> > >
> > > Adi
>
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:
> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:
>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:
> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
> > A clunky, but effective way to track if someone is using the view or
> > trigger is:
> >
> > 1.) VIEW - add a trigger on the view to write to a table everytime the
> > view is accessed
> >
> > 2.) TRIGGER - have the trigger write to a table everytime the trigger
> > fires, as part of the trigger
> >
> > You can include timestamp and system_user data in your table
> >
> >
> > Adi wrote:
> > > I think that those queries might do the job:
> > >
> > > --check stored procedures
> > > select specific_name
> > > from INFORMATION_SCHEMA.ROUTINES
> > > where ROUTINE_DEFINITION like '%ObjectName%'
> > >
> > > --check views
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.VIEWS
> > > where VIEW_DEFINITION like '%ObjectName%'
> > >
> > > The queries will help only for objects in the database, but if you
> > > have an application that uses direct SQL statements, you'll get runtime
> > > errors. You can try and use the profiler to see if external
> > > applications use the old names in direct statements. You can also try
> > > to create a synonym with the old name that will point to the new name
> > > (didn't try it myself, but I think that this should work)
> > >
> > > Adi
>
Wednesday, March 21, 2012
Detecting a change in a particular field in a database
Is it possible, using VB code, to retain a user on a particular pageuntil the status of a certain field in a database is changed?
The scenario I am working on is that a person makes a reservation andhas to wait until that reservation has been accepted or rejected by theadministration.
Dim dsResv As DataSet
dsResv = objResv.DALgetResvStatus(resvId)
While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")
dsResv = objResv.DALgetResvStatus(resvId)
IfdsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
failure.Visible = True
LinkButton1.Visible = True
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
success.Visible = True
LinkButton1.Visible = True
End If
End While
This is how I tried to do it, but it doesnt seem to work.
Any suggestions?
If it is web application, use AJAX to send the request to the server and to get the response asynchronously without submitting the page to server.
For AJAX refer:
http://aspalliance.com/716|||Any other way??
i mean without javascripting
The scenario I am working on is that a person makes a reservation andhas to wait until that reservation has been accepted or rejected by theadministration.
Dim dsResv As DataSet
dsResv = objResv.DALgetResvStatus(resvId)
While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")
dsResv = objResv.DALgetResvStatus(resvId)
IfdsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
failure.Visible = True
LinkButton1.Visible = True
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
success.Visible = True
LinkButton1.Visible = True
End If
End While
This is how I tried to do it, but it doesnt seem to work.
Any suggestions?
If it is web application, use AJAX to send the request to the server and to get the response asynchronously without submitting the page to server.
For AJAX refer:
http://aspalliance.com/716|||Any other way??
i mean without javascripting
Detecting (local) sql-server
Can someone please point me to some code (preferably C#, but C++ or C or VB
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!
-- Rob"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FADFA7C4F67defffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
> VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob
MSSQL is just another service, so you should look for code which shows how
to retrieve service states from C# - it looks as if there's a sample with
Visual Studio:
http://msdn.microsoft.com/library/d...scontroller.asp
If you have multiple instances, then see questions 12 and 13 here for how to
find the instance names:
http://support.microsoft.com/defaul...6&Product=sql2k
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in news:420dc8db$1_1@.news.bluewin.ch:
> MSSQL is just another service, so you should look for code which shows
> how to retrieve service states from C# - it looks as if there's a
> sample with Visual Studio:
> http://msdn.microsoft.com/library/d...ry/en-us/cssamp
> le/html/vcsamprocesscontroller.asp
> If you have multiple instances, then see questions 12 and 13 here for
> how to find the instance names:
> http://support.microsoft.com/defaul...257716&Product=
> sql2k
> Simon
Thank you, Simon! That's *EXACTLY* what I was looking for!
-- Rob
Detect if rows have changed
I want to build some pseudo-update-trigger code that will tell me if the a
row in an Access database has changed. My overall strategy is do successive
imports into SQL Server, join the tables and compare the rows. Is there a
reliable way to determine if a row has changed? I'd like to design a join
where only changed rows are returned. The rows are up to 1000 characters. I
can see that the LEN function won't be reliable. Any help would be
appreciated.I'm only interested in checking if one column in the table has changed.|||Terri
Look at virtual tables within a trigger (deleted,inserted)
"Terri" <terri@.cybernets.com> wrote in message
news:d2ui0q$7al$1@.reader2.nmix.net...
> I want to build some pseudo-update-trigger code that will tell me if the a
> row in an Access database has changed. My overall strategy is do
successive
> imports into SQL Server, join the tables and compare the rows. Is there a
> reliable way to determine if a row has changed? I'd like to design a join
> where only changed rows are returned. The rows are up to 1000 characters.
I
> can see that the LEN function won't be reliable. Any help would be
> appreciated.
>|||I tried creating a trigger on a view based on a table in a linked server.
CREATE TRIGGER
TestTrigger
ON vwComplianceTest
FOR INSERT, UPDATE, DELETE
AS
RAISERROR (50009, 16, 10)
GO
I got an error "invalid object name". The view itself is working. Also tried
to name it [dbo].[vwComplianceTest]. Is it possible to create a trigger on
a view based on a table in a linked server? Is this what you were
suggesting?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eXz4jZmOFHA.2728@.TK2MSFTNGP15.phx.gbl...
> Terri
> Look at virtual tables within a trigger (deleted,inserted)
>|||On Wed, 6 Apr 2005 10:36:56 -0600, Terri wrote:
>I tried creating a trigger on a view based on a table in a linked server.
>CREATE TRIGGER
>TestTrigger
>ON vwComplianceTest
>FOR INSERT, UPDATE, DELETE
>AS
>RAISERROR (50009, 16, 10)
>GO
Hi Terri,
You can't define regular triggers on views, only INSTEAD OF triggers.
I don't know if the linked server messes that up. You'll have to test
it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
row in an Access database has changed. My overall strategy is do successive
imports into SQL Server, join the tables and compare the rows. Is there a
reliable way to determine if a row has changed? I'd like to design a join
where only changed rows are returned. The rows are up to 1000 characters. I
can see that the LEN function won't be reliable. Any help would be
appreciated.I'm only interested in checking if one column in the table has changed.|||Terri
Look at virtual tables within a trigger (deleted,inserted)
"Terri" <terri@.cybernets.com> wrote in message
news:d2ui0q$7al$1@.reader2.nmix.net...
> I want to build some pseudo-update-trigger code that will tell me if the a
> row in an Access database has changed. My overall strategy is do
successive
> imports into SQL Server, join the tables and compare the rows. Is there a
> reliable way to determine if a row has changed? I'd like to design a join
> where only changed rows are returned. The rows are up to 1000 characters.
I
> can see that the LEN function won't be reliable. Any help would be
> appreciated.
>|||I tried creating a trigger on a view based on a table in a linked server.
CREATE TRIGGER
TestTrigger
ON vwComplianceTest
FOR INSERT, UPDATE, DELETE
AS
RAISERROR (50009, 16, 10)
GO
I got an error "invalid object name". The view itself is working. Also tried
to name it [dbo].[vwComplianceTest]. Is it possible to create a trigger on
a view based on a table in a linked server? Is this what you were
suggesting?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eXz4jZmOFHA.2728@.TK2MSFTNGP15.phx.gbl...
> Terri
> Look at virtual tables within a trigger (deleted,inserted)
>|||On Wed, 6 Apr 2005 10:36:56 -0600, Terri wrote:
>I tried creating a trigger on a view based on a table in a linked server.
>CREATE TRIGGER
>TestTrigger
>ON vwComplianceTest
>FOR INSERT, UPDATE, DELETE
>AS
>RAISERROR (50009, 16, 10)
>GO
Hi Terri,
You can't define regular triggers on views, only INSTEAD OF triggers.
I don't know if the linked server messes that up. You'll have to test
it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
detect connectable db servers
Hello,
how I can detect via code (C++, C#) which sql servers like oracle, mssql,
mysql are close to me and connectable within one or two second (timeout to
wait for answers should be variable)?
Thank you.
regards
MarkMark wrote:
> Hello,
> how I can detect via code (C++, C#) which sql servers like oracle, mssql,
> mysql are close to me and connectable within one or two second (timeout to
> wait for answers should be variable)?
> Thank you.
> regards
> Mark
I don't think it's possible. You should already know the names of the servers
you have permission to connect to.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@.attglobal.net
==================|||On Tue, 16 May 2006 19:06:07 +0200, I waved a wand and this message
magically appeared from Mark:
> how I can detect via code (C++, C#) which sql servers like oracle,
> mssql, mysql are close to me and connectable within one or two second
> (timeout to wait for answers should be variable)?
I know how to, but I will not tell you. It is against the law to access
computers if you are not authorised to do so.
--
http://www.munted.org.uk
Take a nap, it saves lives.|||you are all misguided... whether or not it is against the law to access other computers is a stupid argument... hope this helps
the framework (2.0) provides several methods for detecting, but this one is specialized for enumerating sql servers.
http://msdn2.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasou rces.aspx
detect and solve deadlocks
Is there any way by which deadlocks can be detacted and then appropriate
action can be taken (in vb.net code or sql) to avoid it. instead of throwing
error we can revoke the sp call..
thankshttp://www.sql-server-performance.com/deadlocks.asp
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||You can determine what processes are currently blocking other processes by
running sp_who and looking for SPIDs in the [blkby] column.
http://msdn2.microsoft.com/ms174313.aspx
Once you have the SPID of a blocking process, you can use DBCC INPUTBUFFER
to see what command it last executed.
http://msdn.microsoft.com/library/d...
v8y.asp
Here are a couple of articles on tracing deadlocks:
http://support.microsoft.com/defaul...kb;en-us;832524
http://support.microsoft.com/defaul...kb;en-us;271509
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://support.microsoft.com/defaul...kb;en-us;832524
Regards
Amish
*** Sent via Developersdex http://www.examnotes.net ***|||I think all the answers you are getting explain how to figure out what is
causing deadlocks so you can rewrite your applications to avoid them. While
this is definitely the right approach, you seem to be asking what you can do
to detect deadlocks in your code. SQL Server already detects and resolves
deadlocks for you. It does this by picking one of the transactions involved
in the deadlock and terminating it so the other transactions can continue.
The error you get at the client is saying that your transaction is the one
picked as the victim. The right thing to do when you're the deadlock victim
is to restart the transaction. If you have enough information available to
do the transaction again, you can just do it without telling the user that
something happened but if you need the user to re-enter the data to rerun
the transaction then just tell the user what happened and have them redo it.
Avoiding deadlocks through application design is important but in some cases
they are unavoidable and you should code your application to respond
appropriately.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>
action can be taken (in vb.net code or sql) to avoid it. instead of throwing
error we can revoke the sp call..
thankshttp://www.sql-server-performance.com/deadlocks.asp
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||You can determine what processes are currently blocking other processes by
running sp_who and looking for SPIDs in the [blkby] column.
http://msdn2.microsoft.com/ms174313.aspx
Once you have the SPID of a blocking process, you can use DBCC INPUTBUFFER
to see what command it last executed.
http://msdn.microsoft.com/library/d...
v8y.asp
Here are a couple of articles on tracing deadlocks:
http://support.microsoft.com/defaul...kb;en-us;832524
http://support.microsoft.com/defaul...kb;en-us;271509
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://support.microsoft.com/defaul...kb;en-us;832524
Regards
Amish
*** Sent via Developersdex http://www.examnotes.net ***|||I think all the answers you are getting explain how to figure out what is
causing deadlocks so you can rewrite your applications to avoid them. While
this is definitely the right approach, you seem to be asking what you can do
to detect deadlocks in your code. SQL Server already detects and resolves
deadlocks for you. It does this by picking one of the transactions involved
in the deadlock and terminating it so the other transactions can continue.
The error you get at the client is saying that your transaction is the one
picked as the victim. The right thing to do when you're the deadlock victim
is to restart the transaction. If you have enough information available to
do the transaction again, you can just do it without telling the user that
something happened but if you need the user to re-enter the data to rerun
the transaction then just tell the user what happened and have them redo it.
Avoiding deadlocks through application design is important but in some cases
they are unavoidable and you should code your application to respond
appropriately.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>
Detect and convert an empty string (from textbox) to null?
Hi all,
I have this code that I use for my Search function:
SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM t_music
WHERE (@.MUSIC_TITLEISNULL OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')
AND (@.MUSIC_ARTISTISNULL OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))
But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.)
I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet.
Thank you all,
Kenny.
hi,
You could set the text field to NULL if it's empty:
//check if theMUSIC_TITLE field is empty
if (MUSIC_TITLE.Text.Trim() ==""){
MUSIC_TITLE.Text = NULL;
}
|||Hi,
Rewrite your query in this way
SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM t_music
WHERE (@.MUSIC_TITLE=''OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')
AND (@.MUSIC_ARTIST = ''OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))
Hope this will work fine.
|||
Hi all,
I've tried both, try to set TextboxMusic.Text = null and tried to change the SQL statement but both method didn't work.
Any idea?
Thank you all,
Kenny.
|||I think there is a DBNull.Value that you can set the textbox value to if its empty?
|||The best solution is really to make all your columns non-nullable and you won't have this problem at all. In fact, it will avoid many problems that will have you pulling your hair out because of the unintuitive way that NULLs work. For example:
WHERE COLUMN1 <> "ABC"
will not return the row where COLUMN1 is null. The reason is that null means unknown, and unknown means that it could be anything, even ABC. This is by design and is a common feature of all relational databases that I am aware of.
So, I'd recreate your tables with non null columns. You can do this by creating an identical table and doing a SELECT INTO. You should also be able to rename your first table with sp_rename and create the new one under the old name, but I believe (not certain) this will require you to manually recompile all procs that use the table else it will continue to run against the original.
|||The column in the database is not null... What null is the querystring that I pass from a previous page to the search page via textbox. But ADO.NET does not interpretet an empty textbox ("") as null and thus my WHERE statement doesn't work properly.
Thanks,
Kenny.
How are you adding parameters to the database command object? You might need to do something like this:
object paramVal =null;if(text !=string.Empty) paramVal = text;cmd.Parameters.AddWithValue("@.myParam", paramVal);|||I think people misread my question or the answers are something that I haven't fully understand yet... Here is my situation... I have a page with 2 textbox, named TextBoxTitle and TextBoxArtist... Here visitor can enter the name of the song (title) or the artist and click search... They can search by title, artist, or by both... And then I will pass that as a querystring as mydomain.com/search.aspx?title=[ValueFromTextBoxTitle]&artist=[ValueFromTextBoxArtist]
On the search page, I have a SqlDataSource and a GridView to display the results... Here is how I setup my SqlDataSource:
<asp:SqlDataSource ID="DSResults" runat="server" ConnectionString="<%$ ConnectionStrings:notesnhacConnectionString1 %>"
SelectCommand="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM t_music WHERE (@.MUSIC_TITLE = '' OR [MUSIC_TITLE] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST = '' OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%'))">
<SelectParameters>
<asp:QueryStringParameter Name="MUSIC_TITLE" QueryStringField="title" Type="String" />
<asp:QueryStringParameter Name="MUSIC_ARTIST" QueryStringField="artist" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The problem is that, when I try this Select statement in SQL Manager Studio, I was able to search for just the song title, the artist, or both... But on my web page, I can't search for just title or artist, I HAVE to enter both the title and the artist field for the search to return my records... If only one of the field are entered, nothing returned, even though they should.
Thank you all,
Kenny.
|||so if the textbox is empty exclude the querystring parameter. Instead of doing title=&artist=something, do artist=something.
then the value sent to the database will be null instead of string.empty
|||I've tried to exclude one of the querystring if the textbox is empty but it didn't work either :(
Thanks,
Kenny.
Add this attribute to the <asp:QueryStringParameter />
ConvertEmptyStringToNull="true"
|||Thank you all, but I think I'll give up!!! I've tried all the different ways but none seems to be work. I guess I need to find someway to do a code-behind instead of using that SqlDataSource.
Thanks again,
Kenny.
|||
Hi,
>> " I HAVE to enter both the title and the artist field for the search to return my records... If only one of the field are entered, nothing returned, even though they should."
For this situaltion, in my view, I would rather use dynamic sql statement instead of inserting null into the sql condition. See the following sample:
string condition1="";string condition2="";string sql1 ="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM t_music WHERE 1=1";if(textboxTitle.Text!=""){ condition1=" and MUSIC_TITLE like '%"+ textboxTitle.Text +"%'" ;}else if(textboxArtist.Text!=""){ condition2=" and MUSIC_ARTIST like '%"+ textboxArtist.Text +"%'" ;}sql1+=condition1;sql1+=condition2;In this way, you can search for just title or artist.
Hope this helps.
Thanks.|||
This code is susceptible to sql-injection, and I would absolutely not use this code. Change it to use a parameterized query.
Detect (local) SQL Server
Can someone please point me to some code (preferably C#, but C++ or C or VB
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!
-- RobHi Rob
Try using the the ServiceController class to check to see if the MSSQLServer
service is running (ServiceControllerStatus.Running).
Cheers
J.
"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FAE91589AFBdefffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!
-- RobHi Rob
Try using the the ServiceController class to check to see if the MSSQLServer
service is running (ServiceControllerStatus.Running).
Cheers
J.
"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FAE91589AFBdefffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob
Saturday, February 25, 2012
Designing a Code Table
I have a language table 'CODE_LANGUAGE' where we defines languages.
CREATE TABLE [dbo].[CODE_LANGUAGE] (
[language_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[language_name] [varchar(20)] NOT NULL
) ON [PRIMARY]
GO
I need to design a table for Location codes "CODE_LOCATION". This table need
to have these attributes:
location_id
location_name
location_desc
active_flag
external_value
Problem is that location_desc can be in English or French or Spanish. So
what would be the best way to resolve this problem?
I can think of two solutions:
Solution1:
=======
Add 2 columns in 'CODE_LOCATION' table: "id" column and "language_id"
column. The "id" column will be an identity column and will be the primary
key. The "language_id" column will be foreign key to "language_id" column in
'CODE_LANGUAGE' table.
CREATE TABLE [dbo].[CODE_LOCATION] (
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[location_id] [smallint] NOT NULL ,
[location_name] [varchar(20)] NOT NULL ,
[language_id] [smallint] NOT NULL,
[location_desc] [varchar(255)] NOT NULL ,
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Solution2:
=======
Create a seperate table "LANGUAGE_LOCATION" which stores description of
location codes in different languages. Its primary key will be foreign key
in 'CODE_LOCATION' table.
CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
[lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[language_id] [smallint] NOT NULL ,
[location_id] [smallint] NOT NULL ,
[location_desc] [varchar(255)] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CODE_LOCATION] (
[location_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[location_name] [varchar(20)] NOT NULL ,
[lang_loc_id] [int] NOT NULL REFERENCES LANGUAGE_LOCATION(lang_loc_id),
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Which solution is better and why?
It is my understanding that both solutions are in 3rd normal form. Is it
correct?
Do you guys have any better solution?
ThanksI'm trying to understand the problem better.
>Problem is that location_desc can be in English or French or Spanish. So
>what would be the best way to resolve this problem?
For one location, is there going to be ONE location_desc in ONE
language, or several with one in EACH language?
Roy|||Let's fix that first table. You should have found the ISO language
codes when you did research -- in about 3 seconds with a slow
connection to Google. The codes are CHAR(3).
Is there a very good reason you threw out all the ISO-11179 conventions
about putting the attribute in a postfix? What is that reason?
Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
newbies use to spit on RDBMS and Dr. Codd.
I also see that you want to write with flags like SQL was assembly or C
code. Major mindset error.
Your next mistake will be saying things like "XX_loc_id" not knowing
that an attribute cannot be both an identifier and a location.
One column for each language in the same table?|||On 23 Feb 2006 18:19:38 -0800, "--CELKO--" <jcelko212@.earthlink.net>
wrote:
>One column for each language in the same table?
And when the requirement to add German and Italian comes along, what
then?
Roy 8-)|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:e9nsv1d96cbrv3qok72pb3schg8v0dce4m@.
4ax.com...
> I'm trying to understand the problem better.
>
> For one location, is there going to be ONE location_desc in ONE
> language, or several with one in EACH language?
> Roy
For one location there will be several location_desc with one in EACH
language.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140747578.311798.118740@.v46g2000cwv.googlegroups.com...
> Let's fix that first table. You should have found the ISO language
> codes when you did research -- in about 3 seconds with a slow
> connection to Google. The codes are CHAR(3).
>
I did not know about ISO language codes. After your email I research it and
decided that i dont want to use it. By using my table I can differentiate
between american english and canadian english.
> Is there a very good reason you threw out all the ISO-11179 conventions
> about putting the attribute in a postfix? What is that reason?
>
I did not know about ISO-11179 conventions. I am going to research these
naming conventions.
> Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
> newbies use to spit on RDBMS and Dr. Codd.
>
This is part of Solution1 i come up with. I wrote this email to ask if this
is right approach. My guess is that you dont like Solution1. Am i right?
> I also see that you want to write with flags like SQL was assembly or C
> code. Major mindset error.
> Your next mistake will be saying things like "XX_loc_id" not knowing
> that an attribute cannot be both an identifier and a location.
>
> One column for each language in the same table?
>
For one location there will be several location_desc with one in EACH
language. So if there is Location 'Mississauga' then there could be 3
location_desc for it: one in US English; one in CAN French and one in
Spanish. Keeping this in mind please look at the 2 solutions I come up with
and answer these questions:
1) Which solution is better and why?
2) It is my understanding that both solutions are in 3rd normal form. Is it
correct?
3) Do you guys have any better solution?
Thanks|||>Solution2:
>=======
>Create a seperate table "LANGUAGE_LOCATION" which stores description of
>location codes in different languages. Its primary key will be foreign key
>in 'CODE_LOCATION' table.
>CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
> [lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
> [language_id] [smallint] NOT NULL ,
> [location_id] [smallint] NOT NULL ,
> [location_desc] [varchar(255)] NOT NULL
> ) ON [PRIMARY]
>GO
>For one location there will be several location_desc with one in EACH
>language.
Then the proper solution is a table similar to LANGUAGE_LOCATION in
your second solution, but not quite. I would never, ever, create an
IDENTITY column on that table. You have a perfectly good PK in
(language_id,location_id). I can go along with surrogate keys in
circumstances that justify them, but this seems completely
inappropriate to me.
Roy
CREATE TABLE [dbo].[CODE_LANGUAGE] (
[language_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[language_name] [varchar(20)] NOT NULL
) ON [PRIMARY]
GO
I need to design a table for Location codes "CODE_LOCATION". This table need
to have these attributes:
location_id
location_name
location_desc
active_flag
external_value
Problem is that location_desc can be in English or French or Spanish. So
what would be the best way to resolve this problem?
I can think of two solutions:
Solution1:
=======
Add 2 columns in 'CODE_LOCATION' table: "id" column and "language_id"
column. The "id" column will be an identity column and will be the primary
key. The "language_id" column will be foreign key to "language_id" column in
'CODE_LANGUAGE' table.
CREATE TABLE [dbo].[CODE_LOCATION] (
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[location_id] [smallint] NOT NULL ,
[location_name] [varchar(20)] NOT NULL ,
[language_id] [smallint] NOT NULL,
[location_desc] [varchar(255)] NOT NULL ,
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Solution2:
=======
Create a seperate table "LANGUAGE_LOCATION" which stores description of
location codes in different languages. Its primary key will be foreign key
in 'CODE_LOCATION' table.
CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
[lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[language_id] [smallint] NOT NULL ,
[location_id] [smallint] NOT NULL ,
[location_desc] [varchar(255)] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CODE_LOCATION] (
[location_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[location_name] [varchar(20)] NOT NULL ,
[lang_loc_id] [int] NOT NULL REFERENCES LANGUAGE_LOCATION(lang_loc_id),
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Which solution is better and why?
It is my understanding that both solutions are in 3rd normal form. Is it
correct?
Do you guys have any better solution?
ThanksI'm trying to understand the problem better.
>Problem is that location_desc can be in English or French or Spanish. So
>what would be the best way to resolve this problem?
For one location, is there going to be ONE location_desc in ONE
language, or several with one in EACH language?
Roy|||Let's fix that first table. You should have found the ISO language
codes when you did research -- in about 3 seconds with a slow
connection to Google. The codes are CHAR(3).
Is there a very good reason you threw out all the ISO-11179 conventions
about putting the attribute in a postfix? What is that reason?
Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
newbies use to spit on RDBMS and Dr. Codd.
I also see that you want to write with flags like SQL was assembly or C
code. Major mindset error.
Your next mistake will be saying things like "XX_loc_id" not knowing
that an attribute cannot be both an identifier and a location.
One column for each language in the same table?|||On 23 Feb 2006 18:19:38 -0800, "--CELKO--" <jcelko212@.earthlink.net>
wrote:
>One column for each language in the same table?
And when the requirement to add German and Italian comes along, what
then?
Roy 8-)|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:e9nsv1d96cbrv3qok72pb3schg8v0dce4m@.
4ax.com...
> I'm trying to understand the problem better.
>
> For one location, is there going to be ONE location_desc in ONE
> language, or several with one in EACH language?
> Roy
For one location there will be several location_desc with one in EACH
language.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140747578.311798.118740@.v46g2000cwv.googlegroups.com...
> Let's fix that first table. You should have found the ISO language
> codes when you did research -- in about 3 seconds with a slow
> connection to Google. The codes are CHAR(3).
>
I did not know about ISO language codes. After your email I research it and
decided that i dont want to use it. By using my table I can differentiate
between american english and canadian english.
> Is there a very good reason you threw out all the ISO-11179 conventions
> about putting the attribute in a postfix? What is that reason?
>
I did not know about ISO-11179 conventions. I am going to research these
naming conventions.
> Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
> newbies use to spit on RDBMS and Dr. Codd.
>
This is part of Solution1 i come up with. I wrote this email to ask if this
is right approach. My guess is that you dont like Solution1. Am i right?
> I also see that you want to write with flags like SQL was assembly or C
> code. Major mindset error.
> Your next mistake will be saying things like "XX_loc_id" not knowing
> that an attribute cannot be both an identifier and a location.
>
> One column for each language in the same table?
>
For one location there will be several location_desc with one in EACH
language. So if there is Location 'Mississauga' then there could be 3
location_desc for it: one in US English; one in CAN French and one in
Spanish. Keeping this in mind please look at the 2 solutions I come up with
and answer these questions:
1) Which solution is better and why?
2) It is my understanding that both solutions are in 3rd normal form. Is it
correct?
3) Do you guys have any better solution?
Thanks|||>Solution2:
>=======
>Create a seperate table "LANGUAGE_LOCATION" which stores description of
>location codes in different languages. Its primary key will be foreign key
>in 'CODE_LOCATION' table.
>CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
> [lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
> [language_id] [smallint] NOT NULL ,
> [location_id] [smallint] NOT NULL ,
> [location_desc] [varchar(255)] NOT NULL
> ) ON [PRIMARY]
>GO
>For one location there will be several location_desc with one in EACH
>language.
Then the proper solution is a table similar to LANGUAGE_LOCATION in
your second solution, but not quite. I would never, ever, create an
IDENTITY column on that table. You have a perfectly good PK in
(language_id,location_id). I can go along with surrogate keys in
circumstances that justify them, but this seems completely
inappropriate to me.
Roy
Subscribe to:
Posts (Atom)