how to form query?

i am using this statement
select dateadd(dd,1,20010331)
and it's throwing an error
Arithmetic overflow error converting expression to data type datetime.
what's wrong?sql server wants to have date strings, not integers

select dateadd(dd,1,'2001-03-31')|||i am using this statement

select dateadd(dd,1,20010331)


and it's throwing an error

Arithmetic overflow error converting expression to data type datetime.

what's wrong?You're missing quotes:

select dateadd(dd,1,'20010331')

How to for date is not null or nothing condition

How can i write a condition for like isdate(Fields!DueDate.value)

I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.

Thank you very much for the information.

*********************************************

=IIF(isdate(Fields!DueDate.Value), ................

********************************************

Reddy,

Try

=IIf(IsDate(Fields!DueDate.value),"True","false")

This should work for you.

Ham

|||

Sorry should have read it all.

=IIf(Fields!DueDate.value is nothing,"False",IsDate(Fields!DueDate.value),"True","false"))

Ham

|||

There was a program error, I left out the next IIF statement

IIf(Fields!DueDate.value is nothing,"False",IIf(IsDate(Fields!DueDate.value),"True","false"))

How to fix this error? (There is already an open DataReader associated with this Command w

This is my code:

1If Session("ctr") = False Then23 Connect()45 SQL = "SELECT * FROM counter"6 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"7 dbRead()89 If dbReader.HasRows = True Then1011 dbReader.Read()12 hits = dbReader("hits")13 hits = hits + 114 dbClose()1516 SQL = "UPDATE counter SET hits=" & hits17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"18 dbExecute()1920 Else2122 SQL = "INSERT INTO counter(ipaddress,hits)"23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"24 dbExecute()2526 End If2728 Session("ctr") = True2930 End If

 
1Public Sub Connect()2 Conn = New SqlConnection("Initial Catalog=NURSETEST;User Id=sa;Password=sa;Data Source=KSNCRUZ")3 If Conn.State = ConnectionState.Open Then4 Conn.Close()5 End If6 Conn.Open()7 End Sub89 Public Sub Close()10 Conn.Close()11 Conn = Nothing12 End Sub1314 Public Sub dbExecute()15 dbCommand = New SqlCommand(SQL, Conn)16 dbCommand.ExecuteNonQuery()17 End Sub1819 Public Sub dbRead()20 dbCommand = New SqlCommand(SQL, Conn)21 dbReader = dbCommand.ExecuteReader22 End Sub2324 Public Sub dbClose()25 SQL = ""26 dbReader.Close()27 End Sub
 

You should always call the Close method when you have finished using theDataReader object. and also set dbReader Object = nothing and re intialized the object on next use

|||

1 If Session("ctr") = False Then
2
3 Connect()
4
16 SQL = "UPDATE counter SET hits=hits+1"
17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"

SQL = SQL & " IF @.@.Rowcount=0"
22 SQL =SQL & " INSERT INTO counter(ipaddress,hits)"
23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"
24 dbExecute()
25 Close()

27
28 Session("ctr") = True
29
30 End If

sql

How to Fix the "Can't access Database because its Read Only" problem

The error comes up like this -

System.Data.SqlClient.SqlException .... because the database is read-only

( I'm putting this here so searches can find it easier)

I got this idea from a related thread, but they used a NETWORK user that is obsolete in 2007.

I found that you have to edit the actual file properties on the ASPNETDB.MDF and ASPNETDB.LDF files so that the ASPUSER has full access. i.e. on the file properties, security tab, hit the Add button and type in ASP.NET then return. Back at the security properties, click on the 'Full Control' checkbox at the top. That took care of it for me.

This was after checking the IIS control panel for the properties on the folder that contained my application and App_Data folders to be sure that the "Write" checkbox was already checked, it was

Of course this assumes that you have installed the express version of SQL from MicrosoftAre you using SQL Server server instance or user instances, I am a bit confused as you are talking about LDF files and the App-Data folder on the other hand.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

My project is a webservice that I created with Studio 2005, in the default configuration if you have a database, its put into an APP_DATA folder in your webservice folder.

Its in that folder where the ASPNETDB.MDF and LDF files are located and it is there where the permissions must be set in this case. As I am serving files from a virtual directory I setup in IIS, the permission apply to the actual file location on my computer. The same would be true, though, if the files were copied to the wwwroot/myWebService/APP_DATA folder on the actual default web site on my computer.

To directly answer your question, the MDF and LDF files are the actual database file and log file for the SQL Express database that I'm using for my application. When creating a SQL-using project in Studio 2005, the system will automagically make these for you within your project and you can then access them directly in your APP_DATA folder. So as to your question about server or user instances, I am not sure that it applies as this is a SQL Express install (the free one from microsoft); maybe that corrsponds to a user instance in your environment (?)

Perhaps someone else on this thread knows exactly what you're talking about

How to fix error 207 in ms sql server 2000 with out applying SP4 service

How to fix error 207 in ms sql server 2000 with out applying SP4
service
pack 4,on SQL SERVER 2000 with service pack 3a - current environment ?hi
Microsoft has indentified this as a bug,
see the link
http://support.microsoft.com/kb/821535
VT
knowledge is power, share it
http://oneplace4sql.blogspot.com/
"Eckhart" <n.kopalley@.gmail.com> wrote in message
news:1179309867.330165.206820@.n59g2000hsh.googlegroups.com...
> How to fix error 207 in ms sql server 2000 with out applying SP4
> service
> pack 4,on SQL SERVER 2000 with service pack 3a - current environment ?
>

How to fix error 207 in ms sql server 2000 with out applying SP4 service

How to fix error 207 in ms sql server 2000 with out applying SP4
service
pack 4,on SQL SERVER 2000 with service pack 3a - current environment ?hi
Microsoft has indentified this as a bug,
see the link
http://support.microsoft.com/kb/821535
VT
knowledge is power, share it
http://oneplace4sql.blogspot.com/
"Eckhart" <n.kopalley@.gmail.com> wrote in message
news:1179309867.330165.206820@.n59g2000hsh.googlegroups.com...
> How to fix error 207 in ms sql server 2000 with out applying SP4
> service
> pack 4,on SQL SERVER 2000 with service pack 3a - current environment ?
>

How to fix dbcc checkcatalog error (Msg 3853)

After upgrading two of my SQL2000 databases to SQL2005, I see the following
error when running dbcc checkdb (which runs dbcc checkcatalog):
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of row
(role_principal_id=16384,member_principa
l_id=1) in sys.database_role_members
does not have a matching row (principal_id=16384) in sys.database_principals
.
From what I've read, dbcc checkcatalog errors cannot be fixed automatically.
Usually you have to re-create the object. But most of what I've seen on this
error refers to tables and stored procedures, things that are more easily
re-creatable.
How do I figure out what role is causing the problem and then how can I
resolve the error?
Thanks.I haven't seen this error before but it seems to indicate that the db_owner
role is missing. Do you see db_owner when you execute:
SELECT * FROM sys.database_principals
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> After upgrading two of my SQL2000 databases to SQL2005, I see the
> following
> error when running dbcc checkdb (which runs dbcc checkcatalog):
> Msg 8992, Level 16, State 1, Line 1
> Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> row
> (role_principal_id=16384,member_principa
l_id=1) in
> sys.database_role_members
> does not have a matching row (principal_id=16384) in
> sys.database_principals.
> From what I've read, dbcc checkcatalog errors cannot be fixed
> automatically.
> Usually you have to re-create the object. But most of what I've seen on
> this
> error refers to tables and stored procedures, things that are more easily
> re-creatable.
> How do I figure out what role is causing the problem and then how can I
> resolve the error?
> Thanks.|||You're right ... all of the db_* roles seem to be missing. I upgraded three
SQL2000 databases using the detach/attach method. One database has all the
proper roles (and db_owner is principal_id 16384) but the other two are
missing all of the 16xxx principal_id roles.
The databases seem to be working fine (they are for PeopleSoft applications)
even without those roles.
What can I do to fix this?
"Dan Guzman" wrote:

> I haven't seen this error before but it seems to indicate that the db_owne
r
> role is missing. Do you see db_owner when you execute:
> SELECT * FROM sys.database_principals
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
>
>|||> What can I do to fix this?
In the good ole days, I'd have recommended some at-your-own-risk system
table hacking. This isn't allowed in SQL 2005 so your only options are to
either contact Microsoft support or copy your users and objects to new
databases you know to be good.
It would be nice to know how this happened. If you still have old copies of
the SQL 2000 db files around, that might help diagnosis.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...[vbcol=seagreen]
> You're right ... all of the db_* roles seem to be missing. I upgraded
> three
> SQL2000 databases using the detach/attach method. One database has all the
> proper roles (and db_owner is principal_id 16384) but the other two are
> missing all of the 16xxx principal_id roles.
> The databases seem to be working fine (they are for PeopleSoft
> applications)
> even without those roles.
> What can I do to fix this?
> "Dan Guzman" wrote:
>|||I ended up re-creating the database from scratch and then manually reloaded
my tables. That seems to have resolved the problem: all of the proper roles
are there and the dbcc checkcatalog completes successfully.
Thanks.
"Dan Guzman" wrote:

> In the good ole days, I'd have recommended some at-your-own-risk system
> table hacking. This isn't allowed in SQL 2005 so your only options are to
> either contact Microsoft support or copy your users and objects to new
> databases you know to be good.
> It would be nice to know how this happened. If you still have old copies
of
> the SQL 2000 db files around, that might help diagnosis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...
>
>sql

How to fix dbcc checkcatalog error (Msg 3853)

After upgrading two of my SQL2000 databases to SQL2005, I see the following
error when running dbcc checkdb (which runs dbcc checkcatalog):
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of row
(role_principal_id=16384,member_principal_id=1) in sys.database_role_members
does not have a matching row (principal_id=16384) in sys.database_principals.
From what I've read, dbcc checkcatalog errors cannot be fixed automatically.
Usually you have to re-create the object. But most of what I've seen on this
error refers to tables and stored procedures, things that are more easily
re-creatable.
How do I figure out what role is causing the problem and then how can I
resolve the error?
Thanks.I haven't seen this error before but it seems to indicate that the db_owner
role is missing. Do you see db_owner when you execute:
SELECT * FROM sys.database_principals
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> After upgrading two of my SQL2000 databases to SQL2005, I see the
> following
> error when running dbcc checkdb (which runs dbcc checkcatalog):
> Msg 8992, Level 16, State 1, Line 1
> Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> row
> (role_principal_id=16384,member_principal_id=1) in
> sys.database_role_members
> does not have a matching row (principal_id=16384) in
> sys.database_principals.
> From what I've read, dbcc checkcatalog errors cannot be fixed
> automatically.
> Usually you have to re-create the object. But most of what I've seen on
> this
> error refers to tables and stored procedures, things that are more easily
> re-creatable.
> How do I figure out what role is causing the problem and then how can I
> resolve the error?
> Thanks.|||You're right ... all of the db_* roles seem to be missing. I upgraded three
SQL2000 databases using the detach/attach method. One database has all the
proper roles (and db_owner is principal_id 16384) but the other two are
missing all of the 16xxx principal_id roles.
The databases seem to be working fine (they are for PeopleSoft applications)
even without those roles.
What can I do to fix this?
"Dan Guzman" wrote:
> I haven't seen this error before but it seems to indicate that the db_owner
> role is missing. Do you see db_owner when you execute:
> SELECT * FROM sys.database_principals
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> > After upgrading two of my SQL2000 databases to SQL2005, I see the
> > following
> > error when running dbcc checkdb (which runs dbcc checkcatalog):
> >
> > Msg 8992, Level 16, State 1, Line 1
> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> > row
> > (role_principal_id=16384,member_principal_id=1) in
> > sys.database_role_members
> > does not have a matching row (principal_id=16384) in
> > sys.database_principals.
> >
> > From what I've read, dbcc checkcatalog errors cannot be fixed
> > automatically.
> > Usually you have to re-create the object. But most of what I've seen on
> > this
> > error refers to tables and stored procedures, things that are more easily
> > re-creatable.
> >
> > How do I figure out what role is causing the problem and then how can I
> > resolve the error?
> >
> > Thanks.
>
>|||> What can I do to fix this?
In the good ole days, I'd have recommended some at-your-own-risk system
table hacking. This isn't allowed in SQL 2005 so your only options are to
either contact Microsoft support or copy your users and objects to new
databases you know to be good.
It would be nice to know how this happened. If you still have old copies of
the SQL 2000 db files around, that might help diagnosis.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...
> You're right ... all of the db_* roles seem to be missing. I upgraded
> three
> SQL2000 databases using the detach/attach method. One database has all the
> proper roles (and db_owner is principal_id 16384) but the other two are
> missing all of the 16xxx principal_id roles.
> The databases seem to be working fine (they are for PeopleSoft
> applications)
> even without those roles.
> What can I do to fix this?
> "Dan Guzman" wrote:
>> I haven't seen this error before but it seems to indicate that the
>> db_owner
>> role is missing. Do you see db_owner when you execute:
>> SELECT * FROM sys.database_principals
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
>> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
>> > After upgrading two of my SQL2000 databases to SQL2005, I see the
>> > following
>> > error when running dbcc checkdb (which runs dbcc checkcatalog):
>> >
>> > Msg 8992, Level 16, State 1, Line 1
>> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
>> > row
>> > (role_principal_id=16384,member_principal_id=1) in
>> > sys.database_role_members
>> > does not have a matching row (principal_id=16384) in
>> > sys.database_principals.
>> >
>> > From what I've read, dbcc checkcatalog errors cannot be fixed
>> > automatically.
>> > Usually you have to re-create the object. But most of what I've seen on
>> > this
>> > error refers to tables and stored procedures, things that are more
>> > easily
>> > re-creatable.
>> >
>> > How do I figure out what role is causing the problem and then how can I
>> > resolve the error?
>> >
>> > Thanks.
>>|||I ended up re-creating the database from scratch and then manually reloaded
my tables. That seems to have resolved the problem: all of the proper roles
are there and the dbcc checkcatalog completes successfully.
Thanks.
"Dan Guzman" wrote:
> > What can I do to fix this?
> In the good ole days, I'd have recommended some at-your-own-risk system
> table hacking. This isn't allowed in SQL 2005 so your only options are to
> either contact Microsoft support or copy your users and objects to new
> databases you know to be good.
> It would be nice to know how this happened. If you still have old copies of
> the SQL 2000 db files around, that might help diagnosis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...
> > You're right ... all of the db_* roles seem to be missing. I upgraded
> > three
> > SQL2000 databases using the detach/attach method. One database has all the
> > proper roles (and db_owner is principal_id 16384) but the other two are
> > missing all of the 16xxx principal_id roles.
> >
> > The databases seem to be working fine (they are for PeopleSoft
> > applications)
> > even without those roles.
> >
> > What can I do to fix this?
> >
> > "Dan Guzman" wrote:
> >
> >> I haven't seen this error before but it seems to indicate that the
> >> db_owner
> >> role is missing. Do you see db_owner when you execute:
> >>
> >> SELECT * FROM sys.database_principals
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> >> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> >> > After upgrading two of my SQL2000 databases to SQL2005, I see the
> >> > following
> >> > error when running dbcc checkdb (which runs dbcc checkcatalog):
> >> >
> >> > Msg 8992, Level 16, State 1, Line 1
> >> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> >> > row
> >> > (role_principal_id=16384,member_principal_id=1) in
> >> > sys.database_role_members
> >> > does not have a matching row (principal_id=16384) in
> >> > sys.database_principals.
> >> >
> >> > From what I've read, dbcc checkcatalog errors cannot be fixed
> >> > automatically.
> >> > Usually you have to re-create the object. But most of what I've seen on
> >> > this
> >> > error refers to tables and stored procedures, things that are more
> >> > easily
> >> > re-creatable.
> >> >
> >> > How do I figure out what role is causing the problem and then how can I
> >> > resolve the error?
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
--
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIA
Data Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am no
t able to know
which line is giving the problem. How do I make QA show me the offending lin
e.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIAsql

How to find the triggers in a database

One of the databases I work with has several triggers and one of them is
throwing an error. I thought I knew which table it is on, but that table has
no triggers. Is there an easy way to identify all the triggers in a
database?
Much obliged,
Geoff.select * from sysobjects where xtype ='tr'
http://sqlservercode.blogspot.com/|||Hi
Try
select name from dbo.sysobjects
where Xtype='TR'
lookup OBJECTPROPERTY command which has one opf the many parameters like
'TableHasUpdateTrigger'
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||Run this query on any db and you will get the listin of all tables that hav
triggers on them
select TBL.name as "Table", TRG.name as "Trigger"
from sysobjects TRG
inner join sysobjects TBL on TRG.parent_obj = TBL.id
where TRG.xtype = 'tr'
order by TBL.name, TRG.name
Robert
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
has
> no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||http://www.aspfaq.com/2105
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||select * from sysobjects where OBJECTPROPERTY(id, N'IsTrigger') = 1
if you then know the name of the trigger you can write
select * from sysobjects where id = object_id(N'[dbo].[TriggerName]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1|||Thanks. Good link.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135174145.929774.170520@.o13g2000cwo.googlegroups.com...
> select * from sysobjects where xtype ='tr'
>
> http://sqlservercode.blogspot.com/
>|||Thanks.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:OICVJjjBGHA.2356@.tk2msftngp13.phx.gbl...
> Run this query on any db and you will get the listin of all tables that
> hav
> triggers on them
> select TBL.name as "Table", TRG.name as "Trigger"
> from sysobjects TRG
> inner join sysobjects TBL on TRG.parent_obj = TBL.id
> where TRG.xtype = 'tr'
> order by TBL.name, TRG.name
> Robert
>
> "Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
> news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> has
>|||sp_msforeachtable 'exec sp_helptrigger ''?'''
Regards
Amish

How to find out the reason for error 17883?

Hello, ereryone

I use MSDE in my system, and sometimes the following error occurs in MSDE. After that, I can not even access MSDE with the osql command.

Can anyone give some advice as how to deal with this kind of error or how to find the real reason? I have checked MS homepage, but I can not get the details of it.

Error: 17883, Severity: 1, State: 0 The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x003B70F8

Thanks in advance.

check this: http://support.microsoft.com/kb/319892/

How to find logged errors and not-logged erros

Alert don't fire if error is not logged.
How to find logged errors and not-logged erros '
Errors in severity level 20 to 25 are always logged?
ThanksFor logged errors, look in the application log and the SQL
Server error log. Messages that aren't logged you won't find
anywhere. They aren't logged so they aren't recorded
anywhere.
Fatal severity level errors (20 -25), are logged in the
error log and the application log.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:
>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros '
>Errors in severity level 20 to 25 are always logged?
>Thanks|||And one other thing to add...they are stored in the
sysmessages table in master. The dlevel indicates if they
are logged or not.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:
>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros '
>Errors in severity level 20 to 25 are always logged?
>Thanks|||Wich is the value for logged error?
Is 128 ?
Thanks
"Sue Hoegemeier" wrote:
> And one other thing to add...they are stored in the
> sysmessages table in master. The dlevel indicates if they
> are logged or not.
> -Sue
> On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
> favero@.discussions.microsoft.com> wrote:
> >Alert don't fire if error is not logged.
> >How to find logged errors and not-logged erros '
> >Errors in severity level 20 to 25 are always logged?
> >
> >Thanks
>|||Yup...128 is logged.
-Sue
On Wed, 16 Mar 2005 07:17:04 -0800, "andrea favero"
<andreafavero@.discussions.microsoft.com> wrote:
>Wich is the value for logged error?
>Is 128 ?
>Thanks
>"Sue Hoegemeier" wrote:
>> And one other thing to add...they are stored in the
>> sysmessages table in master. The dlevel indicates if they
>> are logged or not.
>> -Sue
>> On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
>> favero@.discussions.microsoft.com> wrote:
>> >Alert don't fire if error is not logged.
>> >How to find logged errors and not-logged erros '
>> >Errors in severity level 20 to 25 are always logged?
>> >
>> >Thanks
>>

How to find logged errors and not-logged erros

Alert don't fire if error is not logged.
How to find logged errors and not-logged erros ?
Errors in severity level 20 to 25 are always logged?
Thanks
For logged errors, look in the application log and the SQL
Server error log. Messages that aren't logged you won't find
anywhere. They aren't logged so they aren't recorded
anywhere.
Fatal severity level errors (20 -25), are logged in the
error log and the application log.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:

>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros ?
>Errors in severity level 20 to 25 are always logged?
>Thanks
|||And one other thing to add...they are stored in the
sysmessages table in master. The dlevel indicates if they
are logged or not.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:

>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros ?
>Errors in severity level 20 to 25 are always logged?
>Thanks
|||Wich is the value for logged error?
Is 128 ?
Thanks
"Sue Hoegemeier" wrote:

> And one other thing to add...they are stored in the
> sysmessages table in master. The dlevel indicates if they
> are logged or not.
> -Sue
> On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
> favero@.discussions.microsoft.com> wrote:
>
>
|||Yup...128 is logged.
-Sue
On Wed, 16 Mar 2005 07:17:04 -0800, "andrea favero"
<andreafavero@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Wich is the value for logged error?
>Is 128 ?
>Thanks
>"Sue Hoegemeier" wrote:

How to find logged errors and not-logged erros

Alert don't fire if error is not logged.
How to find logged errors and not-logged erros '
Errors in severity level 20 to 25 are always logged?
ThanksFor logged errors, look in the application log and the SQL
Server error log. Messages that aren't logged you won't find
anywhere. They aren't logged so they aren't recorded
anywhere.
Fatal severity level errors (20 -25), are logged in the
error log and the application log.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:

>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros '
>Errors in severity level 20 to 25 are always logged?
>Thanks|||And one other thing to add...they are stored in the
sysmessages table in master. The dlevel indicates if they
are logged or not.
-Sue
On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
favero@.discussions.microsoft.com> wrote:

>Alert don't fire if error is not logged.
>How to find logged errors and not-logged erros '
>Errors in severity level 20 to 25 are always logged?
>Thanks|||Wich is the value for logged error?
Is 128 ?
Thanks
"Sue Hoegemeier" wrote:

> And one other thing to add...they are stored in the
> sysmessages table in master. The dlevel indicates if they
> are logged or not.
> -Sue
> On Tue, 15 Mar 2005 07:51:03 -0800, "andrea favero" <andrea
> favero@.discussions.microsoft.com> wrote:
>
>|||Yup...128 is logged.
-Sue
On Wed, 16 Mar 2005 07:17:04 -0800, "andrea favero"
<andreafavero@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Wich is the value for logged error?
>Is 128 ?
>Thanks
>"Sue Hoegemeier" wrote:
>

how to find all the FKs of a table?

I need to delete all the rows of a table, but the table has FK and I get this
error:
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY
constraint.
So first I want to delete all the FKs. How can I know which FKs were set in
this table? I have found something in the Diagram section, but can I list all
the FKs with a command?
ThanksHi
You can look at the INFORMATION_SCHEMA views such as
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = 'MyTable'
John
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks|||from an old post:
if object_id('usp_findreferences'­,'p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/*****************************­******************************­**********/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/*****************************­******************************­**********/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.tab­le_name,
c2.column_name
from information_schema.constraint_­column_usage c1 join
information_schema.referential­_constraints r on
c1.constraint_name=r.constrain­t_name
join information_schema.constraint_­column_usage c2 on
r.unique_constraint_name=c2.co­nstraint_name
where c1.table_name=coalesce(@.tbname­,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.tab­le_name,
c2.column_name
from information_schema.constraint_­column_usage c1 join
information_schema.referential­_constraints r on
c1.constraint_name=r.unique_co­nstraint_name
join information_schema.constraint_­column_usage c2 on
r.constraint_name=c2.constrain­t_name
where c1.table_name=coalesce(@.tbname­,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
-oj
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks