Hi Recently I have moved sql 2005 from one box to another with bigger hdd space and more memory. Since then I am getting the attached error message when I Execute all the job.
I am migrating the SQL server 7 to SQl server 2005 (side by side upgrade).... I copied all the jobs including the maintenance jobs .is that the right way ? do we need to copy the maintenance jobs as well ??? or do we have to create the maintenance plan which will create the new jobs itself ????? please Guide ....... its urgent ....
I've just set up 2 new SQL 7.0 servers, and my new maintenance jobs - backups, optimisations, consistency check jobs etc - are all mysteriously failing. I've created them both with the Maintenance Wizard, and again by hand. I've attempted manual and scheduled runs. All to no avail. Nor do they populate the sysmainthistory table, although they're configured to do so. The mystery is that we have successfully installed some user DTS processes, and THEIR jobs work. In order to resolve the problem, I've:
(1) ensured the Agent service is running;
(2) ensured adequate space on the drive etc;
(3) verified that sqlmaint.exe exists in Mssqlinn;
(4) unchecked the 'attempt to repair minor errors' box (I read this was a known cause of the problem);
I've got some clues to go on, but nothing conclusive;
'sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.'
Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Additionally, both servers hold databases that I imported from another server-INCLUDING the msdb, and BOTH have the same problems and same error messages. This cannot be a coincidence.
2 SQL2000 ent sp4 servers. Try to 'one step' to export all jobs and related maintenance plans from serverA to serverB, via backup msdb from A and restore to B.
After restore msdb to serverB, I saw all jobs and related maintenance plans, but the jobs are not runable.
Do I have to save jobs scripts one by one from A, then rerun on B one by one?
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
I'm working with SQL 2000 and am just learning about Maintenance Plans (MP). They seem convenient, but after some time, I'm wondering if they're the best approach long-term. Here are my experiences.
Using the MP Wizard, I created a plan with tasks from all the dialogs:
I was puzzled to find 4 jobs were created, each with just 1 step, and staggered starting times. I expected to find 1 job with 4 steps. So, brimming with confidence, I did just that. I combined all 4 into 1 job, deleted the 3 other MP created jobs, and checked for any job-specific details in the code. However now when I open the MP, I get this pop-up:
"One or more of the jobs created for this plan has had additional steps added to it. It is not recommended that jobs created by the maintenance plan be modified in any way."
Okay, fair warning. Yet it appears the job and all steps run successfully, both on demand, and on a schedule. So now I'm wondering if jobs always need a MP. If I don't mind working with xp_sqlmaint syntax, it appears the only thing I'm giving up is the MP history. But I expect job history and '-WriteHistory' will minimize that loss.
I searched BOL, this Forum, and Google, and found a couple articles. One author preferred the ease of the Wizard, another preferred the control and added features of T-SQL, but both created a MP in their examples. So I'm hoping some experienced DBAs can advise.
If I create a job with multiple steps, and no MP, are there important things I give up or problems I create? Is this approach a bad idea in SQL 2005?
At this stage, I don't need replication or other advanced features. Just simple database maintenance.
Environment: SQL Server 2005 Enterprise Edition x64, 3 server cluster. Two active servers with seperate instances and one passive server. SQL Server was installed on the two active servers.
Problem: When I fail over either of my instances to the passive server in the cluster my maintenance jobs fail to run and there are error messages in the application event viewer "SSIS Subsystem failed to load". I am guessing that all of the needed components are not installed on the passive server? Is this a close guess? If so, exactly what components are missing and do you have to have another license to install them?
I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".
How can I add entities to my Sqlce- database?
I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.
What do I have to change to make successfully a SaveChanges()?
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
I've a problem with auto-generated key. In my parent table, some lines have been deleted. I would like to add new lines with the same content in order to restore my database, but I cannot decide on the id value which is an auto-generated key. Is there a solution to set an auto-generated key with a specific value ?
In Column I there is a sequence of numbers from 1 to 3 with a step of 1 In Column 2 there is a sequence of numbers from 100 to 120 with a step of 2 In Column 3 there is a name that is repeated accross the whole column
Can I generate such a table with just a single SQL Statement . I don't see any reason why it should not be possible. We know the logic to generate all the numbers of every row. It would be possible using a while loop , but that is be tedious and I am looking for a better way.
One application I can think of is, Suppose I want to find out all deleted primary key ids from a table or say if I have a set (1,2,3, 7,8,9) , and wish to know the missing numbers in the range 1 to 9. If an sql like above is possible I could use it in a left join to get me the desired output.
Could anyone tell me if it is possible and if not , why not ?
Is there any way to look at behind-the-scene SQL query that gets generated by SSRS engine to pull the data out of the data source (particularly SQL Server).
So far, I only know that you can see the query generated by semantic query engine by changing trace option in configuration file.
Hi I have a windows application. I am using following code right after setting the LocalReport ReportPath. Report rendering is too slow if use following line of code and Report viewer does not show spiny and the text that says €śReport is being generated.€?
I have created a report and deployed it successfully to a seperate machine with SSRS on. Both my dev machine and the SSRS machine are in the same workgroup. I can then Browse to the Report Manager though IE and click & run the report (The connection is using integrated security). The report requires a single parameter to be entered which once done works successfully. The report has a single table, an inbedded image and a few static fields in the report header.
If I however add a report viewer control to an aspx test form all is not well. As soon as the page opens I am prompted to enter the parameter which is good news as it shows there has been some communication with the SSRS metabase. I then click the view report button. Using Profiler I have proved that the report Stored Proc. is running with the correct parameter value. The report viewer however seems to hang and only displays "Report is being generated" without displaying the results. It does this even with a timeout set of 1000ms.
I don't think this is a security issue as both the Report Manager works and the test form prompts me for the parameter and effectively runs the report SP. I can only assume it is a problem with returning the report to the browser.
I created a PDA application with a database, which has a table with a uniqueidentifier field and primarykey.
While doing the bulk insert from dataset into sql mobile database, It is inserting the record but it is not inserting the id which was entered into the sql server 2005 database, instead the id by creating a new id and the code is as below.
conAdap = new SqlCeDataAdapter(strQuery, conSqlceConnection);
SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(conAdap);
I have a report which doesn't work. It has a datatset based on a store procedure. The store-procedure take 10 to 50 second to execute, depending on the parameters.
But, I can't never see my report as i wait more than 10 minutes and it's never generated.
So, I have think that maybe it could be because of the matrix inside the report, so I have try to create another report which have the same dataset but with nothing inside, just a textbox, and it doesn't work too !! So now, i'm very disapointed and I don't know what to do ! Someone can help me please ? It's very important.
I created a new DataSet object using the wizard and had no probs, it's very straightforward. I created a GetProducts() method and also added a GetProductCount() method. I read somewhere that when the DataSet is saved, it will generate the TableAdapter classes and store them in the project nested under the DataSet object. This isn't happening, the files aren't there and yes I'm displaying all files. There *is* however the TableAdapter class but it's in not in the project, it's a temporary file w/ the following path:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.
I saved the DataSet several times, compiled the entire solution and even closed the solution yet this temporary file isn't getting saved where it should be. Does anyone have any ideas abt this? I'm eager to start developing my DAL and def want to use these TableAdapter classes.
Can someone explain the generated sprocs of VS2005 if one column can be nullableDependentOfSeqID = @Original_DependentOfSeqID OR ((@IsNull_DependentOfSeqID = 1) AND (DependentOfSeqID IS NULL))In VS2003 the generated sprocs would beDependentOfSeqID = @Original_DependentOfSeqID OR ((@Original_DependentOfSeqID IS NULL) AND (DependentOfSeqID IS NULL))Which is the best?
HI all, if this issue has been solved, please provide me the post, or I've put this issue in a wrong category please let me know. The following is my code: DateTime dtBFWNow = DateTime.Today; String dtBFW_range1 = dtBFWNow.AddDays(-7).ToShortDateString(); String dtBFW_range2 = dtBFWNow.AddDays(3).ToShortDateString(); SqlDataSource1.SelectCommand = "SELECT * FROM [MORTGAGE] WHERE ([BringForwardDate] is not null) and ([BringForwardDate]>= '" + dtBFW_range1 + "' and [BringForwardDate] <= '" + dtBFW_range2 + "')"; lblHeader.Text = "Bring Forward Reminders Due From '" + dtBFW_range1 + "' To '" + dtBFW_range2 + "'"; lblHeader.ForeColor = System.Drawing.Color.FromName("#000000"); GridView1.DataBind(); When I compiled, the following exception occurred: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. and the exception points right at the line: GridView1.DataBind() I use the webhost4life as my server provider. My codes run fine with the server. I mean I uploaded my program to the server, set up and run the program online, everything is fine. But on my PC it gives me the above exception. I can't compile my code. There must be some sort of setting somewhere in my PC that can't handle the exception. Please help. Regards. Joey.
I ran a SP that lists fragmented tables in our DB which listed almost all the tables and indexes in the DB. The list includes index names that start with "_WA_Sys_tablecolumn name_.....". Are these SQL Server generated indexes or are these statitics on the tables? What are they? If these are system generated indexes as I thought they were, how are they generated? Can these indexes be dropped or should one want to? IF so, how? Any effect on the DB performance if dropped?
I am using Enterprise Manager to generate a script of the stored procedures on my database. Under File|Options, I selected Windows Text(ANSI). The file which is generated contains all stored procedures, but there are some procedures which are so long that they wrap to the next line.
When I then try to run this script from ISQL in a batch file, those procedures which wrap error out. Is there any way to generate the script in a more formatted manor, avoiding the wrapping?
I'm having trouble getting the namespaces correct in the XML that is generated by SQL. The XML consists of two parts: a header part with some context information and the payload part with the actual data. With my current SQL script, the namespace is also copied to the payload part.
Here are some DDL and DML scripts to generate two test tables and some test data:
Code: CREATE TABLE dbo.context( idintNOT NULLconstraint pk_dbo_context primary key, namenvarchar(100)NOT NULL ) INSERT INTO dbo.context(id, name) VALUES (1, 'Here comes some great context information.')
[Code] ....
This is my current FOR XML script that generates the XML:
Code: ;WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com') SELECT CEXT.name AS [Context/Name], (SELECT CONT.id AS [Content/Reference],
[Code] ....
This generates this XML (notice that the namespace is repetated in the <Questions> element):
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The XML should be like this:
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request xmlns=""> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The generated XML does not pass the XSD validation.
use dbWebsiteLO SELECT A.vehicleref,A.manufacturer,A.cvehicle_shortmodtext,A.derivative,min(a.ch) as price,A.capid,B.studioimages,(SELECT term FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleref = a.vehicleref and ch = price) FROM vwAllMatrixWithLombardAndShortModel A LEFT OUTER JOIN dbPubMatrix..tblCarImages B on A.capid = b.capid WHERE a.source <> 'LOM' AND a.type = 'car' GROUP BY a.vehicleref,a.manufacturer,a.cvehicle_shortmodtext,a.derivative,a.capid,b.studioimages
I get Invalid column name 'price'. I'm trying to reference the "min(a.ch) as price"
SELECT DISTINCT product.product_id AS Expr1FROM [SELECT product.product_id, product.item_name, product.display,product.selling_price, product.smallpicture, product.main_size,product.main_dinnerware, dbo_coupon_special.coupon_id,dbo_coupon_special.special_price, coupon.effective_date,coupon.expiration_date, dbo_ssc_product.mc_idFROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_specialON product.product_id = dbo_coupon_special.product_id) LEFT JOINcoupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ONdbo_ssc_product.product_id = product.product_idGROUP BY product.product_id, product.item_name, product.display,product.selling_price, product.smallpicture, product.main_size,product.main_dinnerware, dbo_coupon_special.coupon_id,dbo_coupon_special.special_price, coupon.effective_date,coupon.expiration_date, dbo_ssc_product.mc_idHAVING (((product.item_name) Like "*coaster*") AND((product.display)="y"))]. AS [Alias]This is the error I get:The identifier that starts with 'SELECT product.product_id...' is toolong. Maximum lenght is 128Incorrect syntax near the keyword 'AS'Any ideas?Thanks
When editing the data driven subscription query I SOMETIMES get the following error: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Invalid object name '##XX'. The datasource is ok, authentication also, the query works... Why can't it see the temporary table?
Is it possible to reposition the "Report is being generated" graphic?
I have most of my reports set to 1000 px by 1000 px which forces the graphic off of the screen. I would like to be able to see it without having to scroll to it.
Also, is there any way to change the properties of that image? I would think a progress bar that actually showed how far along the report was before completion would be better.
I am working with an .xml file that I want to break up into various tables. A couple of fields in my xml file include html tags (<p> tags specifically). The generated xsd file thinks these are nested xml tags and creates a "P" table for the information contained. Is there a way I can modify the generated xsd within the designer? Or is the only way to fix this is to manually modify the xsd?
There is also an <id> tag within the xml. However, SSIS is not using that <id> as the primary key as it adds data to the various tables that I've specified. Instead it appears to be creating its own primary key - calling it "Id". Is there some way to specify that SSIS use the <id> tag contained within the xml instead of creating its own primary key?
