Wednesday, March 28, 2012
Query with using an Interface between CRM system & SQL Server 2000
system we use which is built on SQL Server 2000. I have
recently started the MCDBA course but do not have enough
experience to answer my own question...
What I have discovered is that all the maintenance of the
tables and relationship have been done through an
interface the CRM System uses which was meant to prevent
my colleagues from having to learn about SQL Server. As a
result when I try to produce diagrams in Enterprise
Manager no links are shown. Do you think this could be an
attribute to the system running slow as I can not see any
relationships between the tables on SQL Server. I think
another reason might be the lack of indexing on some of
the larger tables but any advice you can give would be
great!
Thank you for your time
AnnabelleJust becasue its a CRM database does not mean its been
optinised.
From what you have said there does not seem to be any
foreign keys defined on your system, however you can check
that yourself by
1. Go to EA
2. Select a table that you know should have foriegn key
links
3. Right Click and go to Design Table
4. Press the button on the top of your screen that
says 'Manage Relationships'
Then have a look if any foreign keys have been defined.
A first port of call on speeding up your selected would be
to create a profile then run it through the index tuning
wizard, this will give all the indexs that are needed to
speed things up.
After that you can post specific problems.
Peter
"Happiness is having a large, loving, caring, close-knit
family in another city."
George Burns
>--Original Message--
>We have been experience performance problems on the CRM
>system we use which is built on SQL Server 2000. I have
>recently started the MCDBA course but do not have enough
>experience to answer my own question...
>What I have discovered is that all the maintenance of the
>tables and relationship have been done through an
>interface the CRM System uses which was meant to prevent
>my colleagues from having to learn about SQL Server. As
a
>result when I try to produce diagrams in Enterprise
>Manager no links are shown. Do you think this could be
an
>attribute to the system running slow as I can not see any
>relationships between the tables on SQL Server. I think
>another reason might be the lack of indexing on some of
>the larger tables but any advice you can give would be
>great!
>Thank you for your time
>Annabelle
>.
>
Monday, March 26, 2012
Query which system table to answer this
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
ThanksSqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.
I'll take a look at the code tonight and post again.|||If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.|||this give you all default usage by columns:
select
s.name as colname
,o1.name as tablename
,o2.name as defaultname
from
syscolumns s
inner join
sysobjects o1 on o1.id=s.id
inner join
sysobjects o2 on o2.id=s.cdefault
and this will give you default usage by udts:
exec sp_mshelptype @.typename=null, @.flags='uddt'
Friday, March 23, 2012
Query Tuning problem.
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
>> Guys,
>> I'm stumped. While its not pertinent to the
>> matter, we are running a Vignette content management
>> system on Win2k with Sql 2000 Enterprise on a cluster.
>> The server has 2 Gig of RAM , 2 CPU's and the database
>> size is 1.5G.
>> The query below is fired at login. The indexes
>> seem fine based on the query plan. When I look through
>> profiler, the query below takes a very high # of CPU
>> cycles and reads. It consistently takes more than 1.5
>> seconds to execute the query below. I did a dbcc
pintable
>> for ALL the tables in the query and that did not help
>> either. It seemed to make it worse (3 seconds and above)
>> Any idea what could be the issue here? The server
>> is not really heavily taxed.
>> The tables are small. They have very few rows.
>> VGNCCB_ROLE 939
>> VGNCCB_ROLE_JT 62389
>> VGNCCB_GROUP_USER_JT 1364
>>
>> The problem Query:
>> select
>> ROLE_ID,
>> NAME,
>> DESCRIPTION,
>> CREATE_DATE,
>> MODIFIED_DATE
>> FROM
>> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
>> WHERE
>> ROLE_ID in
>> (select ROLE_ID
>> FROM
>> vign.VGNCCB_ROLE_JT -- Non clustered indexes
>> on USER_NAME AND non clustered on GROUP_ID
>> WHERE
>> USER_NAME = 'testRole' or GROUP_ID in (select
>> GROUP_ID
>> FROM
>> vign.VGNCCB_GROUP_USER_JT -- Non clustered
>> index on USER_NAME
>> WHERE
>> USER_NAME = 'testRole'))
>> I'd appreciate it if someone could follow me in this
>> thread to completion. Such a simple query should not
take
>> this long.
>>
>> TIA,
>> Jack
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
--
(Please reply only to the newsgroup)
Wednesday, March 21, 2012
Query Tool from SQL Server 2K Missing in 2k5 Edition?
My favorite tool ever in SQL 2000 was a little utility, a front end if you will for querying the system tables. It was under the tools menu and if you typed say 'account', you could find all objects containing the name 'account'- stored procs, tables, columns, etc. A GREAT little tool that few people seemed to use. Now I have 2K5, can't remember its proper name to google it by, and can't seem to find it anywhere, though I suspect it is burried somewhere.
Anyone seen this one in SQL 2K and remember its name, or better yet, seen it in 2K5 and can point me there?
Thanks in advance,
Jeff
Moving the thread to the Tools forum.|||I think you are thinking of object search, this is currently not part of the SQL Server 2005 toolset.|||Yep, that's the one...
Object Search. Dang I used that a lot, too bad they left it out of 2k5.
Do you happen to know is there anything similar in 2k5 to get the job done or is it down the querying the systables manually?
Thanks,
Jeff
sqlQuery Tool from SQL Server 2K Missing in 2k5 Edition?
My favorite tool ever in SQL 2000 was a little utility, a front end if you will for querying the system tables. It was under the tools menu and if you typed say 'account', you could find all objects containing the name 'account'- stored procs, tables, columns, etc. A GREAT little tool that few people seemed to use. Now I have 2K5, can't remember its proper name to google it by, and can't seem to find it anywhere, though I suspect it is burried somewhere.
Anyone seen this one in SQL 2K and remember its name, or better yet, seen it in 2K5 and can point me there?
Thanks in advance,
Jeff
Moving the thread to the Tools forum.|||I think you are thinking of object search, this is currently not part of the SQL Server 2005 toolset.|||Yep, that's the one...
Object Search. Dang I used that a lot, too bad they left it out of 2k5.
Do you happen to know is there anything similar in 2k5 to get the job done or is it down the querying the systables manually?
Thanks,
Jeff
Monday, March 12, 2012
Query to get Operating System information?
I need to get my operating system from SQL Server. Would love to have @.@.OperatingSystem or ServerProperty('WindowsVersion') or something like that.
Anyone have any ideas?
thanks ahead,
One of the simple way...
Select Substring(@.@.Version , CharIndex('Edition on ',@.@.Version) + len('Edition on ') ,LeN(@.@.Version))
If your output :
Windows NT 5.0 = Windows 2000
Windows NT 5.1 = Windows XP
Windows NT 5.2 = Windows 2003 Server & etc.
|||The following query also help you to find the windows os version..
xp_msver N'Platform', N'WindowsVersion'
Friday, March 9, 2012
Query to find invalid objects?
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to tr
y
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all o
f
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the S
QL Server world, so it
could mean different things. The short story is "no", there is no such table
..
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all o
f
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are calle
d
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the
SQL Server world, so it
> could mean different things. The short story is "no", there is no such tab
le...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
[vbcol=seagreen]
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are cal
led
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>|||As already posted, the sp_depends and sysdependencies tables (and the 2005 c
atalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL,
deferred name
resolution etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...[vbcol=seagreen]
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are cal
led
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>
Query to find invalid objects?
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to try
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
could mean different things. The short story is "no", there is no such table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are called
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> could mean different things. The short story is "no", there is no such table...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > Is there a system table that holds data on invalid objects/procedures? In
> > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > the invalid objects. Is there anything similar in SQL Server?
> >
> > thanks!
> > Susan
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
> > Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> > could mean different things. The short story is "no", there is no such table...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> > news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > > Is there a system table that holds data on invalid objects/procedures? In
> > > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > > the invalid objects. Is there anything similar in SQL Server?
> > >
> > > thanks!
> > > Susan
> >
> >|||As already posted, the sp_depends and sysdependencies tables (and the 2005 catalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL, deferred name
resolution etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so
>> it
>> could mean different things. The short story is "no", there is no such table...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
>> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>> > Is there a system table that holds data on invalid objects/procedures? In
>> > Oracle, I can query dba_objects table where status='INVALID' to find all of
>> > the invalid objects. Is there anything similar in SQL Server?
>> >
>> > thanks!
>> > Susan
>>
Wednesday, March 7, 2012
Query Timeouts after adding a logging table and SP
I hope you can help with some good suggestions. I have a system that was
working fine as far as performance, but after I added the following table and
SP, with a call to this SP from my Insert/Update/Delete queries, I started
getting timeout errors and the system came to a crawl. The first time it
happened I could not get it working until we rebooted the server. After that
it happened again the following day and then I backed out the calls to the
AddTableLog proc. Here is the table def and proc:
TableLogs definition
3 MessageId int 4 0
0 Message varchar 500 1
0 MessageDate datetime 8 1
0 AppLoggedInUser varchar 100 1
0 ComputerName varchar 100 1
0 CompLoggedInUser varchar 100 1
Proc to add to above table
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
Here is an example of how I was using the above log tables:
CREATE PROCEDURE [dbo].[nf_AddAttendance]
@.PatientId varchar(20),
@.AttendDate datetime,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, GetDate(), Session_User,@.ComputerName,
@.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Is there a better way to do this. I wanted to be able to log who actually
made the change and what computer they where at. So I pass that info in.
Would using a Trigger be faster at doing this? Or am I on the right track.
Thanks for any suggestions.
Michael LeeI forgot to mention that we are using SQL Server 2000.
Thanks again.
Michael Lee
Query Timeout
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>
Saturday, February 25, 2012
Query that adds data for every user
Dear Masters;
I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users?
Ex: Messages Table
ID UserID Msg
1 100 "Hello"
2 100 "Hi"
3 200 "Hello"
Thanks..
try this to add your message to current message
UPDATE Messages
SET msg=msg + ' Please update your infos '
where userID in (100,200)
or this to create new content for message
UPDATE Messages
SET msg = 'Please update your infos '
where userID in (100,200)
Thanks
|||
Thanks for repliying;
But may I write a select statement for example in the in() section?
Cause I may need to add for all users so I gues it should be something like "for" statement.. But if I can write select statement inside in() That may solve...
I'll try thanks
|||Yes, you can try for example
where userID in (select UserID from messages where userID>100)
but your select statement should return only one column.
Thanks
|||I don't think you want an update statement for this purpose. If you update where UserID = 100, you will update both records 1 and 2. You will need to do multipl einsert statements. You can do this inside a stored procedure that you pass paramaters to (ie. parameter 1 is the message and parameter 2 is a comma seperated list of user ids). If you are not comfortable with stored procedures, you will need to construct and execute multiple insert statements in your code. Don't forget to use a transaction in case of failure.|||
Hi;
I guess I'm a little bit confused:) Could you explain me more deeply? I think I'm ok with stored procedures but what should this stored procedure include?
don't you think if J's solution works? I mean why shloud it update both records?
Thanks
|||
In your example table structure you have the same userID for 2 first records so f you do update for userID=100 both records will be updated. If you would like to do it for specific one you have to use ID column to select only one record. It was mistake or you can have two entries for the same userID in your table?
Thanks
|||Oh i c;
Thanks, I will actually insert a new row I'm not gonna update so problem solved thanks...
Thanks to both masters;)
|||Here is an article on how to write a stored proc to take a comma seperated value (CSV) list into a stored proc. You should be able to adapt this for your own needs.Monday, February 20, 2012
Query System Tables for FK/Not For Replication
being enforced for replication. Is this information stored in a system
table?
MichelleCheck the CnstIsNotRepl property using OBJECTPROPERTY function.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"michelle" <michelle@.nospam.com> wrote in message
news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
> I'm trying to ensure that none of the 47 foreign keys in my database are
> being enforced for replication. Is this information stored in a system
> table?
> Michelle
>|||Thanks!
SELECT * FROM dbo.sysobjects
WHERE
OBJECTPROPERTY(id, N'CnstIsNotRepl') = 0
AND xtype = 'FK'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%231CxJMsuEHA.4008@.TK2MSFTNGP10.phx.gbl...
> Check the CnstIsNotRepl property using OBJECTPROPERTY function.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to ensure that none of the 47 foreign keys in my database are
> > being enforced for replication. Is this information stored in a system
> > table?
> >
> > Michelle
> >
> >
>
Query System Tables for FK/Not For Replication
being enforced for replication. Is this information stored in a system
table?
MichelleCheck the CnstIsNotRepl property using OBJECTPROPERTY function.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"michelle" <michelle@.nospam.com> wrote in message
news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
> I'm trying to ensure that none of the 47 foreign keys in my database are
> being enforced for replication. Is this information stored in a system
> table?
> Michelle
>|||Thanks!
SELECT * FROM dbo.sysobjects
WHERE
OBJECTPROPERTY(id, N'CnstIsNotRepl') = 0
AND xtype = 'FK'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%231CxJMsuEHA.4008@.TK2MSFTNGP10.phx.gbl...
> Check the CnstIsNotRepl property using OBJECTPROPERTY function.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
>
Query System Tables for FK/Not For Replication
being enforced for replication. Is this information stored in a system
table?
Michelle
Check the CnstIsNotRepl property using OBJECTPROPERTY function.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"michelle" <michelle@.nospam.com> wrote in message
news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
> I'm trying to ensure that none of the 47 foreign keys in my database are
> being enforced for replication. Is this information stored in a system
> table?
> Michelle
>
|||Thanks!
SELECT * FROM dbo.sysobjects
WHERE
OBJECTPROPERTY(id, N'CnstIsNotRepl') = 0
AND xtype = 'FK'
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%231CxJMsuEHA.4008@.TK2MSFTNGP10.phx.gbl...
> Check the CnstIsNotRepl property using OBJECTPROPERTY function.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:e33lCGsuEHA.1420@.TK2MSFTNGP10.phx.gbl...
>