Database Discussion Boards

how to find a trigger is enabled or disabled for a particular table

smjp30-Jan-07 18:08

30-Jan-07 18:08

In sql server 2000 how to find wheather a trigger is enabled or disabled for a particular table...
I found that there is stored procedure sp_helptrigger tablename for getting the details of how many triggers present in a particular table...

But i want to find out find wheather a trigger is enabled or disabled for a particular table...

plz help me out

this is smjp

Re: how to find a trigger is enabled or disabled for a particular table

Ware@Work5-Feb-07 10:58

Ware@Work

5-Feb-07 10:58

As I understand it triggers are enabled if they exist. You disable it by removing it. It might not fire if there was an error in original command or prior triggers run (multiple can run for same sql statement).

Thanks,

Ware

SQL Query Help

Hulicat30-Jan-07 13:36

Hulicat

30-Jan-07 13:36

I am trying to join the two following select statements into a single result that would look like the following:

Ticket Number, Open Issues, Email

meeting this criteria from table "job_ticket" WHERE job_ticket.location_id = '5' and status_type_id like '[1-2]'

Both select statements run fine alone

SELECT job_ticket.job_ticket_id 'Ticket Number', job_ticket.subject 'Open Issues'
FROM job_ticket
WHERE job_ticket.location_id = '5' and status_type_id like '[1-2]'

Select email from client where client_id ='5'

Data from Table "Job_ticket"
ticket_id
subject
client_id
location_id
Status_type_id

Data from table "Client"
client_id
email

I am trying to pull the client.email via the job_ticket.client_id into the 1st sql statement:
SELECT job_ticket.job_ticket_id 'Ticket Number', job_ticket.subject 'Open Issues'
FROM job_ticket
WHERE job_ticket.location_id = '5' and status_type_id like '[1-2]'

I have tried various methods and read through "books online", however; I am not making much progress.

What is the best way to do this?

Any information would be greatly appreciated.

Thanks and Regards,
H

Regards,
Hulicat

Re: SQL Query Help

andyharman30-Jan-07 19:50

andyharman

30-Jan-07 19:50

SELECT job_ticket.job_ticket_id 'Ticket Number',
   job_ticket.subject 'Open Issues', Client.Email
FROM job_ticket
INNER JOIN Client
 ON Client.Client_Id = Job_Ticket.Client_Id
WHERE job_ticket.location_id = '5'
AND status_type_id like '[1-2]'
ORDER BY job_ticket.job_ticket_id

Regards
Andy

Re: SQL Query Help

Hulicat31-Jan-07 9:10

Hulicat

31-Jan-07 9:10

Thanks a ton....

This one was killing me.....

That was it.......

Thanks and Regards

Regards,
Hulicat

DataGrid and DataGridView

Blekk30-Jan-07 12:48

Blekk

30-Jan-07 12:48

Hi,

I have got the Apress book Beginning C# Databases: from Novice to Professional, and have been very pleased with it, But because it was written before Microsoft Visual C# 2005 came out, some things are a bit out of date. I have managed to get around most of them except this one:

It tells me to add a DataGrid to the designer view, but all I could find was DataGridView, and after some scripting, I have to "Bind the data grid at run time" using the script:

dataGrid1.SetDataBinding(dataset1, "employees");

I understand what the code is doing but the class DataGridView does not have a SetDataBinding function and so do not know how to bind the DataGridView1 object to the dataset.

Please help.

Thanks,

Any reply is appreciated.

I will also post this in the C# section

Re: DataGrid and DataGridView

smjp30-Jan-07 18:45

smjp

30-Jan-07 18:45

first create a dataset
then in page_load event type this
for windows application
adapter1.Fill(dataset1,"employees");
dataGridView1.DataSource = dataset1.Tables["employees"]

for web application
gridview1.datasource=dataset1.Tables["employees"]
gridview1.databind()

this is code maker

Re: DataGrid and DataGridView

Blekk31-Jan-07 7:48

Blekk

31-Jan-07 7:48

thanks for the help,

But I found out how to do it another way before your post:

dataGridView.DataBindings.Add("datasource",dataSet1,"customers");

Question about using PIVOT in SQL

leckey30-Jan-07 11:33

leckey

30-Jan-07 11:33

In Access I am doing a crosstab query which translated to SQL is:

TRANSFORM Sum(Adjust_Sold_Sold_Subtraction.Qty) AS SumOfQty<br />
SELECT Adjust_Sold_Sold_Subtraction.PartNumber, Adjust_Sold_Sold_Subtraction.Model, Sum(Adjust_Sold_Sold_Subtraction.Qty) AS [Total Of Qty]<br />
FROM Adjust_Sold_Sold_Subtraction<br />
GROUP BY Adjust_Sold_Sold_Subtraction.PartNumber, Adjust_Sold_Sold_Subtraction.Model<br />
PIVOT Adjust_Sold_Sold_Subtraction.Vendor;

The table is data entry table. The user can choose the Vendor from a drop down box.

problem: if in this session the user does not have a record for every vendor in the list it causes problems later on when I try to insert the records into a table (which has a field for each possible vendor).

Is there a way to force the code to recognize all the vendors, even if not all vendors have been entered in the source table?

Or is there a way (some type of if statement?) when trying to do the append that somehow I can program 'the vendor XYZ doesn't exist in the source table, but it exists in the destination table so just stick a 0 in there.'

_________________________________________________________________
Dick Cheney looks like a high school calculus teacher with persistent heartburn.

can i return dataReader in ado.net method on web serviec ?

PavanPareta30-Jan-07 3:05

PavanPareta

30-Jan-07 3:05

Please help me i have a question?
Confused | :confused:

can i return dataReader in ado.net method on web serviec ?

Pavan Pareta

Re: can i return dataReader in ado.net method on web serviec ?

Pete O'Hanlon30-Jan-07 3:48

Pete O'Hanlon

30-Jan-07 3:48

Why would you want to? A DataReader is meant to be a forward-readonly reader that efficiently allows you to get data out of the database. A web-service, (when a method is marked as a [WebMethod], is typically meant to return a synchronous value.

Now, what you could do is have a collection that is populated by the DataReader and then returned from the WebMethod.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Re: can i return dataReader in ado.net method on web serviec ?

Parwej Ahamad30-Jan-07 4:55

Parwej Ahamad

30-Jan-07 4:55

Why u not return Dataset instead of datareader...........

Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com

Ennis Ray Lynch, Jr.30-Jan-07 11:37

Ennis Ray Lynch, Jr.

30-Jan-07 11:37

A data reader contains an open connection to the database whereas the idea of a web service is basically disconnected/stateless. The best you can do with a data reader would be remoting.

File Not Found

How to make constraint for column based on pattern

El'Cachubrey30-Jan-07 2:20

El'Cachubrey

30-Jan-07 2:20

Hi all
In my table i have coulmn which should contain customer e-mail address, can i and how if i can
make for this column constraint based on string pattern (is this achived througth
CONSTRAINT Col LIKE '...'????)

THANK

Re: How to make constraint for column based on pattern

Pete O'Hanlon30-Jan-07 3:44

Pete O'Hanlon

30-Jan-07 3:44

El`Cachubrey wrote:
In my table i have coulmn which should contain customer e-mail address, can i and how if i can
make for this column constraint based on string pattern (is this achived througth
CONSTRAINT Col LIKE '...'????)

Errm. No.

It is not the place of the database engine to enforce things like this because this is a business rule. If you don't want email addresses to look like this, then you need to have a rule that enforces it.

General brains required on SQL Performance

Rob Philpott30-Jan-07 0:55

Rob Philpott

30-Jan-07 0:55

Hey all,

I have a very big table (about 165 million rows) in a Sql Server 2005 database. I also have a number of stored procedures which access this table - mostly 'select' type read only queries.

There is one particular select-based stored proc that gets called repeatedly, and the strange thing is the difference in time it takes to execute. Most of the time its quick, but occasionally its very, very slow. When it gets slow, if I open the proc up in Sql Management studio, and resubmit it by simply hitting F5 - the problem goes away.

I suspect this is because the current execution plan gets trashed and a new more effecient one generated when I resubmit it. By using the query plan viewer it has become apparant that Sql Server does vary its execution plan of this proc even though its specification doesn't change. Sometimes the execution plan is good and sometimes it isn't.

Anyone experienced anything like this or any hints or tips?

Regards,
Rob Philpott.

Re: General brains required on SQL Performance

Pete O'Hanlon30-Jan-07 1:10

Pete O'Hanlon

30-Jan-07 1:10

The problem might not be with this procedure, but with other resources. For instance, if you have a table or lock occurring in one of the tables that is used in the proc then you will find that your procedure will be affected. The execution plan won't change, but your procedure will perform slowly.

What you may want to do is to run SQL Profiler against the database and leave it running for a while. Once you get the problem, then you should be able to analyse what's going on.

As a thought, have you checked the statistics on the table? Is this table frequently updated?

Retrieving sybase data from sql server 2005 instance

basith55529-Jan-07 18:59

basith555

29-Jan-07 18:59

hi i need to select a data from a sybase table into a sql server 2005 table.i dont know whts is the query to do it, let me show u my code.i get error when i do that query

select * from openrowset('MSDASQL','dsn=bodaily;uid=sa;pwd=welco me;Initial Catalog=phoenix;',
' select rim from rm_acct')

this rm_acct is a sybase table

can anyone guide me on this.
thanks

Re: Retrieving sybase data from sql server 2005 instance

andyharman29-Jan-07 22:22

andyharman

29-Jan-07 22:22

I use SQL-Server 2000 at the moment. Our approach was to set up a linked-server then use:
select * from openquery(MySybaseServer, 'select * from Products')

We also found that the Sybase ODBC driver ran faster than the OLEDB driver (although our tests were carried out a few years ago).

Hope this helps.
Andy

Access SUM()

myNameIsRon29-Jan-07 10:08

myNameIsRon

29-Jan-07 10:08

Hi,
I'm using an Access database in my C# win application.

I need to sum values from two different tables and then subtract one of the SUM values from the other.
No problem until I runn into one of the SUM values = null. I need it to return 0 if null is the result of the SUM.

What is the best way to handle this... with an IIF?

IIF(SUM(OrderTotal) IS NULL,0,SUM(OrderTotal)) ???

Thanks for any help you can give.
Ron

Re: Access SUM()

Vasudevan Deepak Kumar29-Jan-07 22:24

Vasudevan Deepak Kumar

29-Jan-07 22:24

http://office.microsoft.com/en-us/access/HA012315151033.aspx[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: Access SUM()

andyharman29-Jan-07 23:40

andyharman

29-Jan-07 23:40

nz(sum(OrderTotal),0)

Re: Access SUM()

myNameIsRon30-Jan-07 19:07

myNameIsRon

30-Jan-07 19:07

Thanks andyharman,

I tried but it came up with an error so I researched it.
Looks like NZ can't be used with SQL OLE DB, just within MS Access program.

ron

Re: Access SUM()

andyharman30-Jan-07 19:44

andyharman

30-Jan-07 19:44

If you are using SQL Server then the syntax is:
isnull(sum(YourColumn), 0)

Brendan Vogt29-Jan-07 5:13

Brendan Vogt

29-Jan-07 5:13

Hi,

I am using SQL Server 2005.

Can someone please explain to me the differenc between a login, and a database user?? I am working through a SQL book and they created a login, then a database user. Is the login always the same as the database user? If not then when will they differ? I have the following piece of code:

-- Create the login Peter
CREATE LOGIN Peter WITH PASSWORD='Abc123';

-- Change the connection context to the database AdventureWorks
USE AdventureWorks;
Go

-- Create the database user Peter, mapped to the login Peter in the database AdventureWorks
CREATE USER Peter FOR LOGIN Peter;

I hope someone can help me clarify this issue.

Regards
ma se

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.