|
Yes, you can put a trigger on a replicated table.
We have a table that is updated via replication approximately every 15 minutes; there is a trigger on the table for inserts to create a subset of the data in a table in another database.
Hope that answers your question.
Tim
|
|
|
|
|
Thank you....
Can i have sample of trigger..
i am using code like this:
CREATE TRIGGER test AFTER INSERT ON temp
FOR EACH ROW BEGIN
INSERT INTO table1 SET fld1 = NEW.fld1;
INSERT INTO table1 SET fld2 = NEW.fld2;
INSERT INTO table1 SET fld3 = NEW.fld3;
END;
will this work...??
|
|
|
|
|
To be perfectly honest, I have no idea...
I didn't write the trigger on our table; I just maintain the app now.
My best advice would be: try it and see what happens.
Tim
|
|
|
|
|
thanx for quick response..
Will the error in trigger affet the original table data insertion..??
|
|
|
|
|
I think if a trigger fails, the original insert fails.
But, again, trial and error.
|
|
|
|
|
When I try to execute the following CREATE TABLE command, even directly in Access' query analyzer, I get the message "Cannot create child list for field Table". I can't find any reference by Googling that doesn't refer to binding data grids or navigating away from a record, which I'm obviously not trying to do here.
CREATE TABLE test ( [code] INTEGER)
I tried adding PRIMARY KEY just in case, but no difference.
The table itself *is* created successfully - it seems to be trying to do something after creating the table and that's what causes the error message.
Any ideas?
|
|
|
|
|
Hello all,
As per the title I am curious to learn how, from a source control/versioning perspective, other people manage DB development projects?
As an example I am currently managing a large project with around 20 developers. This project is primarily developed in C# and VS 2005 and sources are stored/versioned using Subversion. Now this all works for the source, but it doesn't quite fit when it comes to controlling the DB.
Currently we have a "DB Scripts" project, that contains all the scripts needed to build a DB from scratch and these are checked in/out using Subversion. However it is a tad cumbersome.
BTW this is all for SQL2005
|
|
|
|
|
We are a much smaller shop, 5 developers, but we use SQL Compare from Red-Gate for deployment, Source safe for source control (this is very unsatisfactory). We expect to move to Team foundation server soon.
SQL Compare is bloody marvelous. User signs off on UAT DB and it is deployed to production with no problems.
Managing the development cycle is more challenging. When there are changes in UAT and the users want to start another round of changes cn be a nightmare!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi friends
i have a table which have record DistrictName,DistrictDiscription
now the the proble is that i want that this record is to be place in to the
other table which have 4 Column
District_Name1, District_description1,District_Name2, District_description2
so that Odd number of rows should be place in
District_Name1, District_description1
and even number of rows should be place in
District_Name2, District_description2
how can i do this When i have no Primary Key and indexer
How can check the rocord of the table one by one in any loop iteration
wasim khan
|
|
|
|
|
wasimsharp wrote: Odd number of rows should be place in
District_Name1, District_description1
and even number of rows should be place in
District_Name2, District_description2
You can only achieve this if you have the means to order the data in the desired way.
What you are doing doesn't make any sense though. Why do you want to do this? There's probably a better way of solving your problem.
BTW, you should have primary keys defined on your tables. Otherwise, how can you uniquely identify rows for CRUD operations?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi
i have a fild in my db with (image) datatype
i write a sp with this code to compare 2 image fild
select isnull(srl,-1) as srlFng from base_info where img =@img
but this error show when i press check sintax
error 306: the text,ntext,and image data tyoes cannot be compared or sorted, exept ehrn using is null or Like operator
can anyone help me in this problem?
thnx
|
|
|
|
|
1)
EXEC sp_addumpdevice 'DISK','XYZ',
'c:\abc.bak'
BACKUP DATABASE student_database
TO XYZ
WITH DESCRIPTION='def',
STATS
RESTORE DATABASE student_database
FROM XYZ
WITH DBO_ONLY,
NORECOVERY,
STATS
EXEC SP_DBOPTION student_database,'dbo use only','false'
-------------
After executing these query,when
"use student_database"
----
Error message is =the database is in middle of processing...cannot use..
2) I also want to delete sp_addumpdevice--"XYZ" and "abc.bak" after completing the task.
If I delete manually the abc.bak from device, then also when going to use
"EXEC sp_addumpdevice 'DISK','XYZ','c:\abc.bak'"---error message will come as it already exist.
How can i solve this.
|
|
|
|
|
simworld wrote: RESTORE DATABASE student_database
FROM XYZ
WITH DBO_ONLY,
NORECOVERY,
STATS
You restore the database with the 'NORECOVERY'-option - this means that the database remains offline and in recovery-mode (normally used within database-mirroring). Remove this option and the first error-message should disappear.
|
|
|
|
|
|
Hi all,
I have a big table with 30 million rows (and growing) which I need to join to a table containing dates. The result is a bar graph diagram.
But when I join with time over 12 hours the join times out
SELECT table1.time, table1.value
FROM table2 INNER JOIN table1 ON table1.time > table2.time_up AND table1.time < table2.time_down
ORDER BY table1.time
This should result in any thing from a couple of hundreds row to several thousands.
I'm doing this in a Stored Procedure. I've put indexes on table1.time.
Is this join simply to large for SQL Server 2005? Or how can I optimize this search?
|
|
|
|
|
At a guess you have datetime fields with time extensions and need to join on the date only. You killer is the <> comparison.
If this is the case I suggest you convert both dates to a string with a date only format eg
on Convert(varchar(20),table1.time,103) = Convert(varchar(20),table2.time ,103)
If you are using the time as part of your join (ie between 1am and 3pm) then you have a problem and will need to work around it some other method (split the date and time fields, maybe change the time part to int for hours 0-23)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK, thanks.
I'll try that out. Unfourtunally I need to join on hours as well. And in a perfect world on minutes as well.
|
|
|
|
|
Did you use a clustered index for your "time" index on Table1? That would be considerably more efficient that using a non-clustered index.
|
|
|
|
|
WOW! I've tried different indexes on the large table but I never got i right.
Now this baby:
SELECT Intervallvarden.storhet, Intervallvarden.id_intervallvarde, Intervallvarden.varde, Intervallvarden.tid, Intervallvarden.kanal_nr, MatpunktInstrument.instrumenttyp, MatpunktInstrument.instrumentnummer
FROM MatpunktInstrumentParameter
INNER JOIN MatpunktInstrument
ON MatpunktInstrumentParameter.id_matpunkt_instrument_parameter = MatpunktInstrument.id_matpunkt_instrument_parameter
INNER JOIN Intervallvarden
ON Intervallvarden.instrument_typ = MatpunktInstrument.instrumenttyp
AND Intervallvarden.instrument_nr = MatpunktInstrument.instrumentnummer
AND Intervallvarden.tid > MatpunktInstrumentParameter.tid_upp
WHERE (MatpunktInstrumentParameter.id_matpunkt = 2575)
AND (Intervallvarden.tid > '2007-10-01 00:00')
AND (Intervallvarden.tid < '2007-11-30 23:00')
ORDER BY intervallvarden.tid
Runs in two seconds! Table "Intervallvarden" being the 30 milion rows table.
Thanks allot!
|
|
|
|
|
hi all,
i have developed an application in vb.net with Ms-Sql as back end.
on one stage i am facing an proble while i creating an store procedure.
the coding is as under:-
CREATE PROCEDURE ProcedureName
(
@Sid bigint
)
as
declare @Cityid bigint
begin
Select splrid,splrname,splrundrgrup from finactsplrmstr where splrid=@Sid
Select adrsemail from finactadrsmstr where concrnid=@Sid
set @Cityid=(select adrsctyid from finactadrsmstr where concrnid=@Sid)
select * from finactCscmstr where cscid=@Cityid
end
GO
further When i called this procedure in vb.net it gives not proper output,
say i want to display a record using a sql command in a list view.
i want output like this:
Name: Under Group: Email Address: City: State: Country:
the result which received only first select statement i.e Name and Under Group.
kinldy help me.
thanks in advance:
rmshah
Developer
|
|
|
|
|
I'm not sure what your problem is. Are you having difficulty accessing the multiple resultsets returned by the query?
If you are filling a DataSet, each resultset will form a separate DataTable in your DataSet.
If you are using a SqlDataReader, call NextResult to move between resultsets.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
thanks
sorry for delay actualy my system was out of order, further i am sending exact code which i am using
Private Sub sel_rec()
Dim LstviewRcord As ListViewItem
Try
Actgrpcmd = New SqlCommand("FinAct_SplrMstr_Select_ByName", Conn)
Actgrpcmd.CommandType = CommandType.StoredProcedure
Actgrpcmd.Parameters.AddWithValue("@sid", LstviewName.Text)
Actrdr = Actgrpcmd.ExecuteReader
Actrdr.Read()
LstviewRcord = LstvewAct.Items.Add(Actrdr("SupplierId"))
LstviewRcord.SubItems.Add(Actrdr("Suppliername"))
LstviewRcord.SubItems.Add(Actrdr("Supplierundrgrup"))
LstviewRcord.SubItems.Add(Actrdr("csccityname"))
LstviewRcord.SubItems.Add(Actrdr("cscstatename"))
LstviewRcord.SubItems.Add(Actrdr("csccontry"))
Actrdr.NextResult()
LstviewRcord.SubItems.Add(Actrdr("adrsemail"))
Catch ex As Exception
MsgBox(ex.Message)
Finally
Actgrpcmd = Nothing
Actrdr.Close()
End Try
End sub
but i not able to fect all record, only first statement's result i can get.
help me
rmshah
Developer
|
|
|
|
|
r_mohd wrote: but i not able to fect all record, only first statement's result i can get.
Does your stored procedure return multiple records? If so, you need to set up a loop to traverse them.
For instance:
While Actrdr.Read()
' Do stuff
End While
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
still not able to get the record.
here i am giving u more details
i have two tables say tbl_Supplier and tbl_City.
in table city i have stored info about Supplier with city id which comes from tbl_City.
In tbl_city i have stored City name, State name and Country name.
Now I want to display a record in a Listvew, which has following columns.
Supplier Name,Under Group, Email Address, City Name,State Name,Contry Name
i trying to get this using a SP in mssql
Coding of sp and Sql Command already sent u
pl help
rmshah
Developer
|
|
|
|
|
Your stored procedure looks overcomplicated. You could achieve the same result with 1 SELECT statement if you employed JOINs between the tables.
Also, you should consider giving your tables/fields more meaningful names. Your current naming conventions are quite cryptic. It would be difficult for someone else to understand what they mean without having to consult your systems specification.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
