Hi,
i have a few web jobs, that were set up by the DBA that worked here before I did and they have just stopped running and I have no idea why.
The error I get is:
Executed as user: dbo. SQL Web Assistant: Could not establish a local connection to SQL Server. [SQLSTATE 42000] (Error 16804)
Does anyone have any experience with this?
Thanks in advance
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
Is any other ideas ?
Thanks a lot
Alex
Where do I go to modify the T-SQL statement I entered while creating a job with the web assistant wizard?
View 1 Replies View RelatedWe Upgraded our SQL Server 7.0 to 2005 - executed Script web.sql to create tables and procedures and now we want to execute a webtask.
But it raises the error 16820 "SQL Web Assistant faild because the state of the webtask in msdb..mswebtask is invalid"
How can i fix this
Its Urgent
thanks for your help
HANNES
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).
Thanks.
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
View 2 Replies View RelatedHi,
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?
Any help will be greatly appreciated!!!
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.
S ]-[ /- | ]-[ /- N
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?
View 4 Replies View RelatedI'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.
Any help would be much appreciated.
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.
View 23 Replies View Related
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 would be grateful for any ideas....thanks.
Hi all,
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.
Thanks in advance,
Scott Chang
I have to transfer some data from my SQL Server 7.0 database on NT Pro to a Unix server as a text file. This has to be scheduled as a weekly job.
I can use DTS to transfer data into a text file , but is there a way by which i can export this file to a Unix server also ?
Any suggestions would be appreciated.
Thanks!
Hi friends,
My database had this require ment
Every day my database 12;00pm we will take fullbackup
1:00 O clock differntial backup
2:00 O clok again on diifferntial backup
Every 15(fifteen)Min we will take Transaction log backups
My database is crashed on 2:00 clock we will not take 2:00 clock Tlog backup
we have taken only 1:45 Tlog backup.
Here my database is loss 1:45 to 2:00 clock data
we cannot take this 15min Tlog backup
Any body plz give suggestion on how to recover this 15min data plz help me it very urgent
Regurds
subu
Meti BEST OF THE BEST
Bonjour,CREATE TABLE [dbo].[MAND]([Mat] [varchar](5)[Dur] [varchar](1)) ON [PRIMARY]Mat Dur16030d16030i31217i10000d12000i10000d31217d35000d36000i35000dJe voudrais avoir le resulat suivant (i need this result) :10000 d35000 dCar ils ont tous les deux "d". J'ai beau faire un regroupement (groupby) par Mat avec un having за ne marche pas.Comment faire ?Merci d'avance
View 14 Replies View Relatedhi, i get
Msg 208, Level 16, State 1, Line 1
Invalid object name 'a1'.
i do have a1, what is the problem here?
thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[STP_InvoiceByPono]
AS
BEGIN
SET NOCOUNT ON;
declare @Fieldname varchar(25)
declare @stmt varchar(max)
declare fields cursor for select amounttype from Inv_AmountTypes
set @stmt = 'Select distinct a1.date_, a1.suppliercode, a1.invoiceid, a3.pono'
open fields
fetch next from fields into @fieldname
while @@fetch_status = 0
Begin
set @stmt = @stmt + ',(Select b3.amountfc from
invoice b1 inner join Inv_Detailed b2 on b1.InvoiceID = b2.InvoiceID
inner join Inv_Multiplicity b3 on b2.InvoiceID_Detailed = b3.InvoiceID_Detailed
inner join a1 on a1.InvoiceID = b1.InvoiceID inner join a3 on a3.Pono = b3.Pono
where amounttype = ''' + @fieldname + ''') as ' + @fieldname
fetch next from fields into @fieldname
END
CLOSE Fields
DEALLOCATE Fields
set @stmt = @stmt + ' From Invoice a1 inner join Inv_Detailed a2 on
a1.InvoiceID = a2.InvoiceID inner join Inv_Multiplicity a3 on
a2.InvoiceID_Detailed = a3.InvoiceID_Detailed'
exec(@stmt)
select(@stmt)
END
Almost have my custom membershipprovider finsihed, but have ran into a small snag.Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs a stored procedure on SQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.Im wondering if it has something to do with calling 2 stored procedures consecutivly????::CODE TO FOLLOW::
View 2 Replies View RelatedI have a couple of jobs set on 1 server. I want these jobs to be transferred, or rather copied to another server. Is there a method by which I can just point these jobs to another server, without having to manually create them again ??
Thanks!
I need to get only the Time from the datetime result. I am curently using this:
select right(Convert(varchar(20),getdate(),109),8)
Is there any other built function of SQL that we can use to get this ?
Thanks in advance!
SC
I am having a problem replicating between 2 servers using 1 distributor. I have server X and server Y.
Server X is a publisher, distributor, and supposedly a subscriber, while server Y is a subscriber and
supposed publisher (using Server X as a remote distributor). My logreader task (off server X since
it is the distributor) gives me the error message "Unable to connect to server Y". I was pretty sure it
is possible to publish to the same server that is your distributor am I wrong? If I am not wrong and
this scenario is possible any ideas on why it isn`t working and how to get it working?
Thanks for the help in advance
Levi Akers
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o(
what maybe the problem?
thanks!
Hi,
Having a problem with DTS Wizard crashing in XP, has anyone experienced similiar problems?
Using XP with SP1, transfering excel file to an oracle serve.
I have a table w/ 17K line items. There are only 8.5k I want because each one is duplicated exactly twice. How do I get one of each of these line Items into a new Table?
I tried this:
Select DISTINCT * from DuplicateTable INTO NewTable
And it doesnt work. I am still getting the 17K transported over... any solutions?
I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?
Another question: should I explicitly write this to a table? If yes, how?
************************************************** ******************** ' Visual Basic ActiveX Script '************************************************* **********************
Function Main() Main = DTSTaskExecResult_Success
strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")
oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"
set oCommand.ActiveConnectection = oConn
strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"
oCommand.CommandText = strQuery oCommand.Properties("Page Size") = 99
set oRS.sort ="cn"
i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End Function
repairJob(repairJobID, description)
->('overhaul','blablabla...')
->('gearbox','blablabla...')
sparePart(sparePartID, description ,cost)
->('sp1', 'oil+bolt+...', 100)
->('sp2', 'bolt, clamp...', 200)
->('sp3', 'protector, cover..', 500)
repair(repairID, repairJob, sparePartID, cost, workShopID)
->('r001', 'overhaul', 'sp1', 100, 'w001')
->('r002', 'overhaul', 'sp2', 200, 'w001')
->('r003', 'gearbox', 'sp3', 500, 'w002')
->('r004', 'gearbox', 'sp2', 200, 'w002')
output:
Is it possible to make it?