Hello everybody. I want to use Web Assistant with option "..data Change"
so Web assitant will create trigger.
The database generated from Erwin 3.5.2 and use triggers for integrity.
When I run Web assistant wizard I am getting message ".. can't create trigger becouse existing trigger created with encryption option
sp_helptext tI_prcode (my insert trigger) shows all code in the trigger
where shoild i look to disable encryption option
I found way around this problem
A.Save and delete existing trigger
B.run Web assistant trigger will created
C. Save and delete web assiatnt trigger
d. Insert code from web assitant trigger into code from original erwin
trigger and recreate it
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users. these html files that are generated are processed via a JOB in sql server. I am in process of replacing of the web assistant procedures. Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure. Is there any other possible way to do it?
I used SQL Server's web assistant wizard to create a web assistant job. In this job I chosed "When SQL Server Data Changes" for schedule options. Late, I don't want this job and by right click web assistant job I deleted this job.
However, I have a stored procedure which use the same table with the above deleted web assistant job. Normally, the stored procedure runs only 1 minute. Today it runs more than 5 minute and then failed. Here is the error messages:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++ Server: Msg 16882, Level 11, State 1, Line 0 SQL Web Assistant: Web task not found. Verify the name of the task for possible errors. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
Please somebody can tell me how can I solve this problem (or totally delete the above web assistant job).
HiI've created a stored procedure (see below) which accesses the ActiveDirectory and SQL server to get "real names" back. When I run thestored procedure in Query Analyzer it returns the expected results,however when I try to create a Web Assistant job based upon theprocedure I get the SQL-DMO message:Error 7410 Remote Access not allowed for Windows NT Useractivated bySETUSER.The procedure is being run (and the job created) as the account whichowns the SQL Server installation, and this account has AD adminpermissions.Any suggestions?CREATE VIEW dbo.vw_account_adASSELECT a.Name AS ad_name, dbo.Accounts.*FROM dbo.Accounts INNER JOINOPENQUERY(ADSI,'select SamAccountName, Name FROM''LDAP://w2k-bspad1/ ou=users,ou=bsp,DC=ad,DC=bl,DC=uk'' whereobjectcategory=''person'' ') a ONSUBSTRING(dbo.Accounts.Account_Name,CHARINDEX('', dbo.Accounts.Account_Name) + 1,LEN(dbo.Accounts.Account_Name) - CHARINDEX('',dbo.Accounts.Account_Name)) = a.SamAccountNameCREATE PROCEDURE [dbo].[usp_event_report] ASSET ANSI_NULLS ONSET ANSI_WARNINGS ONSELECT Code_Name, Account_Name + ' ('+ad_Name+')' as 'Account Name',Date_Occured, ResultFROM Usage_Codes, Usage, vw_account_adWHERE Usage.Code_ID = Usage_Codes.Code_IDAND Usage.Account_ID = vw_account_ad.Account_IDAND datepart(month,Date_Occured) = datepart(month,getdate())ORDER BY Code_Name, Account_Name, Date_OccuredGOChloe CrowderThe British Library
Hi, I am converting a schema from oracle to sql server 2005 using Sql Server Migration Assistant but for the conversion of each and every table it is asking me to connect to oracle. How can i convert this schema without again and again specifying the connection? Thanks in advance, Aru
Has anyone ever tried to use this free software to migrate an old ORACLE 8.1.7 database to SQL Server 2005? I was just wondering if there were any "gotchas" I should be aware of before hand. Thanks, Robert
I am trying to migrate an Access 2003 database to SQL Server 2005 and am having various problems:
1. On one table, the migration gives the following on an autonumber field: A2SS0023: SQL Server Migration Assistant for Access Warning Message: Default value '0' added to target column (1), followed later on by A2SS0028: Auto-increment attribute of the column 'MSEQ' was not converted to Identity because the column has a default value.
This table has about 80,000 records.
2. If I include another table in the migration, this one with 3.4 million records (hence our need to upgrade to SQL server 2005), I am getting a timeout error, even when I try to migrate the table by itself. I have verified that all timeout options are set to zero (unlimited time).
Any suggestions would be appreciated.
-- Larry West, MCSD, MCPD, MCTS: SQL Server 2005 [so yes, I should know the answer to this, but I don't], MOUS, FLMI, ACS
I selected my source Oracle database and my target SQL Server database. I click the convert SQL button and it takes about 20 minutes to convert everything. Then what? The Migrate Data button is greyed out. I configured the linked server as is outlined in the instructions. The "Migrate Data" button is always greyed out. I need to get this working soon so that I can convince the management team that moving to SQL Server is the way to go. Thanks.
I have SQL 2000 and installed SQL 2005 Express. Had done some test migrations of Access 2003 to SQL2000 using the tool for that. Upgraded that migration assistant to SQL 2005 (my mistake) and now I have 2 instances of SQL2005 running (according to surface area configuration) and even the server mgr for SQL2000 can see those instances.. but SSMA 2005 refuses to see them.. My migration test project is dead in the water.. Can I get a copy of SSMA for SQL2000 back?..Anybody?.. Thanks..
I've a merge rep running with 4 subscribers for a while. Now I wanted to add another subscriber and when I open the publisher in the pull subscription assistant dialog, there are no publications in it. (even not if i switch to "allow anonymous sub") But every- thing seems to be alright wirh the publication itself! It even doesn't work to select the publication in a pull subs. from an other db on the same server! Any ideas?
i am transfering an Oracle 10g database to SQL Server 2005. i have 2 pcs one with SQL Server 2005 with SSMA For Oracle Extension 3.1 installed, also with Oracle 10g client configured to connect the Oracle database in another pc. the other pc has Oracle 10g database server installed with SSMA For Oracle 3.1. whenever i try to migrate the data there is an exception which says
"Errors: SQL Server cannot access the source table"
i dont understand why its there. i hav successfully converted the schema and loaded them in SQL Server. can anybody tell me a soltuion asap.
I can connect to my Oracle database and I can connect to my SQL Server database. But the "Convert Schema" button stays disabled. What am I doing wrong? Anyone have experience using this tool?
I'm trying to make the business case to move our business databases to SQL Server 2005 from Oracle 9i. The SQL Server Migration Assistant for Oracle will be absolutely necessary for this to happen. However, I am unable to launch the assistant. It tells me I have an old version of java installed - but I don't. It is a new XP machine and I downloaded and installed j2sdk-1_4_2_12-windows-i586-p.exe which did not help (I rebooted). Why does it think I have an old version and how can I get this assistant working.
I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.
I set up a connection file in order to move data from sql to csv files. I should be at the last step, the data flow. but:I don't see any flat file in my destination assistant.
I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).
The file is actually a backend, so nothing to migrate other than tables.
After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.
Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):
[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.
There is nothing wrong, so far as I can determine, with the relationships involved.
I can insert data into the tables using any of the following methods: 1. Directly in Access, in the source backend. 2. Using the original Access frontend application, attached to the source backend. 3. Using VB.Net forms I am developing, in the 'upsized' database. 4. Directly in the Management Studio, in the 'upsized' database.
None of these four methods complains in the least about the relationships which SSMA balks at.
I downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
Hello All, I have to write Trigger for Update, I have two tables, one is for storing records of current values, and one is for storing history of values. How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values. Help me. General Problem
I need to create a set of rows every time a new row is inserted into a table. Example (I think this would work)... select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id) Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values? Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger? Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value. SET @ActionTaken = 'A' IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
Hi All, I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set (Lastname = @Lastname) where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname', 16, 1) ROLLBACK TRAN RETURN END GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR insert,Update AS
IF left('Loan_No',3)='ABC' update Test_TRG set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF' update Test_TRG set loanbook = 'NEWDEF' where loanbook is null
else
update Test_TRG set loanbook =left('Loan_No',3) where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ". The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if The left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=KVS and its null then NEWKVS left(loan_no,3)=MFS and its null then MFS left(loan_no,3)=TCR and its null then TCR left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it l get an error.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR INSERT, UPDATE AS
UPDATE Test_TRG SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG' WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS' WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS' WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 ) END FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK -- Fill in Primary Key or other Join Column(s) WHERE LEFT( i.Loan_No, 3 ) = 'MCG' OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL ) OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'. Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'.
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
