I received this error while trying to view a job's history while using SSMS.
syntax error (ll DB Maint'']) (Microsoft.SqlServer.SmoEnum)
anyone see anything like this? I dropped a database yesterday that was included in the maintenance plan, but forgot to take it out of the plan itself.
Hi
I have a full database backup scheduled at 2AM in the morning
everyday and If I see that job status in Sql Server logs
current activity , it shows me that database has been backed
up at 2AM....But the problem is when I go to jobs and try to
do a view job history it doesn't give me the details of the
job and I didn't clear the job history..Can anyone tell me the
reason why it doesn't show me the job history.
Thanks
RAGHU
How can I allow non SA accounts access to view completed Job History of certain jobs.
Development application owners may receive notification of a Production SQL job failing and have no way to see step "details" of the failed job.
Hello, I browsed the internet for an answer but all I can
find is the mention of a third party tool called log explorer
that cost a grand for a single license ... no thank you,
but basically what i want to do is be able to open the .ldf file
that is created to log transactions when you first create a database.
Or if there is another method to view transaction logs then please mention it.
I tried DBCC Log('database') which did not provide me with much information
which I can use.
Thanks in advance,
Sharp_At_C
Hello,
I have a critical job.
When i take the view job history of that job here it has a colum named as 'duration'. It gives the exact duration of each step it has taken in its last run.
But i have the same job in test environment.
But when i take take the history here i dont see any duration column. how can i add that column.
Thank you
ichayan
Hi guys,
acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...
Can any one tell's me about this...
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.
Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
Is there a place where SQL Server logs errors? When I run my Access MDBagainst a SQL 7 back end, I get ODBC errors, which are sometimes a littledescriptive, sometimes not at all. I usually have to execute code directlyagainst the back end to find out what's really going on. And that's if I'mable to reproduce the error. It would be helpful if SQL logged all errors itgenerates, or if something like that can be set up.Thanks,Neil
View 1 Replies View RelatedWhenever I perform a backup or restore I receive the following message:
"A history record could not be written to msdb.sysbackuphistory..."
What can I do to resolve this error?
getting error when updting VIEW
UNION ALL view 'a.dbo.a'is not updatable because a partitioning column was not found. Severity 16 State 12 Example Line 194
Please advice..
Thanks,
ServerTeam
Hi all
iam creating view as follows
if exists drop view v1
create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date
i want to check before creatuing view but i gives erros as follows
Incorrect syntax near the keyword 'drop'.
'CREATE VIEW' must be the first statement in a query batch.
No rows affected.
Malathi Rao
I keep getting the following error message whenever I try to create a new view from my local machine.
An unexpected error happened during this operation...Query Designer encountere a Query error: Unspecified error
Any help would be greatly appreciated. Thanks
I'm having this problem with SQL Server 2000...
Sample query in view definition:
SELECT somecol1 FROM sometable1
UNION
SELECT somecol2 FROM sometable2
I'm returning more columns in the SELECT than I've put in the sample and all the datatypes match for each column. But...
I get the row error when the view is run. If I run the first part in it's own results are returned. If I run the second on it's own I get results. I only get the error when they are UNIONed. There's no ordering or grouping.
Why would each of them run individually but not UNIONed together?
Also, if I remove ('' instead of table.column) one of the larger nvarchar columns from the first or second query, the UNIONed statement returns results just fine.
Any help would be appreciated.
Thanks,
Mike
Afternoon All
Hoping someone will be able to give me a little advice. I accidently delted a view from one of our application databases (Like you do!!).
I actually repliacated the database 2 month ago onto another server to do some testing. Me thinking I was clever decided to go to the copied database and copy the "syntax" for creating the view and then run it on the database I deleted the view but I get the following message I can't for the life of me work out what the problem is.
Has anyone seen this before ??
Server: Msg 207, Level 16, State 3, Procedure VP_TIMESHTPUNV42, Line 2
Invalid column name 'NAME'.
Server: Msg 207, Level 16, State 1, Procedure VP_TIMESHTPUNV42, Line 2
Invalid column name 'NAME'.
i tried running the statement :
(1)
create view qcostcentre
as
select * from dbo.costcentre.dtblcostcentre
>ERR: Msg 208, Level 16, State 1, Procedure qcostcentre, Line 4
Invalid object name 'dbo.costcentre.dtblcostcentre'.
(2)
create view qcostcentre
as
select * from costcentre.pcusers.dbo.dtblcostcentre
>ERR: Msg 7202, Level 11, State 2, Procedure qcostcentre, Line 4
Could not find server 'costcentre' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
note: pcusers is a user of 2 database (costcentre & datamaster) with dbo_datareader owned & role schema .
Hello, I get this error:
Could not allocate ancillary table for view or function resolution
When I try to open a view that has never had a problem before today. I called Microsoft support and got a hot fix sent to me but unfortunatly it didnt install at all. I get an error:
You do not have any instance of SQL installed on this computer that qualifies for this hotfix.
Please check version and service pack requirements for this hotfix
And according to KB articles this problem should have been fixed by SP4 (which I have). So I dont know what to do. Could someone please help me??
Thanks
Mark
hiii, i am using asp.net 2005 and sql server 2005...i hav a page in which i have used gridview and sql data source..i have written the update command for the same..the problem is when i add the where clause in the query i get an error ,,,,here is the code
UpdateCommand="UPDATE SMEtre_Master SET FirstName =@FirstName, LastName =@LastName, Type_of_SME =@Type_of_SME, Agency_Name =@Agency_Name, Email =@Email, Address =@Address, Phone =@Phone, Mobile =@Mobile, Fax =@Fax, Experience =@Experience, City =@City, State =@State where SME_Id=@SME_Id"
How can a person view the error logs without being an sa?
View 1 Replies View RelatedCASE is not supported Error in a View
In SQL 7, when a view parses the below code, it generates a 'CASE is not Supported' error'; however, it will still generate and show the field with the CASE statements correctly; What might cause this error?
(Other useful info: this code is from a converted SQL 6.5 DB and the SQL 7 is running in compatibility mode, i inserted '' to remove concantenation of NULL problems;)
Example of display:
10132 Hampton, VA: A. Deepak Publishing
SELECT PublisherID, Info = CASE WHEN City IS NOT NULL
THEN City + CASE WHEN State IS NOT NULL
THEN ', ' + State ELSE '' END + ': ' ELSE ''
END + Publisher
FROM tblLibraryPublishers
Thank you!
Llyal
I have a view that is involved in an import. Sometimes the import will fail with the following error:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I can recreate the error just from selecting from the view. Why would something be converting when just selecting from view?
I’m receiving the following message when attempting to run the SQL statement below.
Error report:
SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2"
Failed: Warning: execution completed with warning
-----------------------
CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS
SELECT DISTINCT code, name
FROM
(
SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6
FROM UIP_SEGMENT_VALUES
WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v)
UNION
SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL)
);
----------------
Referenced View Columns:
"SEG_VIEW_PARENTS" ("SEG_NAME", "CODE", "NAME")
Referenced Table Columns:"UIP_SEGMENT_VALUES"
"SEG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_DESC" VARCHAR2(200 BYTE),
"SEG_TYPE" VARCHAR2(20 BYTE),
"SEG_COMPANY" VARCHAR2(20 BYTE)
I am tryung to create a indexed view..
CREATE VIEW vwLookAdmissionRecord
WITH SCHEMABINDING
AS
select episode_key as episode_key, ee.object_key, ee.encounter_begin_dt , cp.procedure from dbo.encounters e
join (
select episode_key as minepisode, min(isnull(e.object_key,e.Object_key_History) ) as object_key, min(e.encounter_begin_dt)as encounter_begin_dt
from dbo.encounters e
join dbo.CD_Procedure CP on CP.Procedure_CD = e.procedure_cd
where cp.procedure > 0
group by e.episode_key
)ee on e.object_key = ee.object_key
join dbo.cd_procedure cp on cp.procedure_cd = e.procedure_cd
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON vwLookAdmissionRecord (episode_key)
but i keep getting an error:
annot create index on view "dbo.vwLookAdmissionRecord" because it references derived table "ee" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.
How can I fix this?
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
Code:
SELECT TOP (100) PERCENT O.EID, O.OStart, O.OEnd, O.OID, T.Title, P.PStatus AS PS, dbo.CalcAge(O.OStart, ISNULL(O.OEnd, CURRENT_TIMESTAMP)) AS ODuration, O.PID,
O.Residence, O.b55, O.SplitItem, O.PeakStaff, O.ResidenceSub, O.Negotiator, O.Supervisor, O.TimeType, O.BreakPM, O.WorkEnd, O.Lunch, O.BreakAM, O.WorkBegin,
[code]....
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE
--------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_GetViewNumberOfAttachments
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @emailMessageID bigint
DECLARE @metaDataStorageID bigint
DECLARE @numberOfAttachments int
DECLARE @AttachmentDetails TABLE
(
emailMessageID bigint,
metaDataStorageID bigint,
numberOfAttachments int
)
DECLARE ATTACHMENT_CURSOR CURSOR
FOR
SELECT emailMessageID, metaDataStorageID
FROM ppaEmailMessage
WHERE hasAttachments='true'
OPEN ATTACHMENT_CURSOR
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
WHILE @@FETCH_STATUS = 0
BEGIN
-- here the table name need to get dynamically the name of the attachment table
-- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically
set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID)
INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments)
VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments)
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
END
CLOSE ATTACHMENT_CURSOR
DEALLOCATE ATTACHMENT_CURSOR
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vw_NumberOfAttachments')
DROP VIEW vw_NumberOfAttachments
GO
CREATE VIEW vw_NumberOfAttachments
AS
SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments
FROM @AttachmentDetails
GO
END
GO
----------------------
I am getting following errors:
-----------
Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57
Incorrect syntax near 'vw_NumberOfAttachments'.
Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3
Must declare the scalar variable "@AttachmentDetails".
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.
-----------
Can anyone please suggest whats wrong in there? Many thanks
I get the following error message:
SQL Exectution error,
Exectuted SQL statement: SELECT...
Source: .Net SqlClient data provider
Message: Timeout expired, timeout period elapsed prior to completion of the operation...
The view is operating on a table of ~250,000 records. The timeout occurs after 45 seconds, and none of the changes I make to the various timeout settings in the server or database seem to affect this.
When I execute the same sql statement in a query window it does complete, requiring about 7 minutes to execute.
How can I configure the view not to timeout when executing this query?
- Jim
Hellow,
This is the siuation:
I have on server A SQL 2005
on server B i have SQL 2000
I want to create in a database on server A a view which uses a linked server to get data from server B.
The linked server works fine.
But when I want to execute the creation of that view i get this error:
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Procedure VW_ASSETTABLE, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK". The provider supports the interface, but returns a failure code when it is used.
I have checked and the collations between the to databases are the same.
Anyone an idea on who to do such thing?
The rights that i have an server B or not that much.
Thanx for the info
I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 .
The code is a follows:
USE [CS_WGO]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo].[ALG_ORG_BEZ_V]
(
[NDL] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
And the Error is:
Msg 1939, Level 16, State 1, Line 1
Index kann für die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist.
For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema.
I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... ?
Thanx ... wvg
I got a problem with my project on reporting services. I installed MS SQL Server 2014 (86x version), Report Builder 3.0 (for creating my reports) and Data Tools for my SQL Server.Now I created a report via Report Builder 3.0 with a SAP NetWeaver as a data source. I could create and view it in Report Builder 3.0.
But when I want to view it on my Report Server it doesn't work. I only get this error."An error has occurred during reporting processing. (Processing Aborted) An attempt has been made to use a data extension 'SAPBW' that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)" ...
I found on the internet a few solutions. I installed the Data Tools for the 86x version of the SQL Server. Didn't work. And when I want to change some coding in "RSReportDesigner.config" it says that I'm not allowed to save the changes in this directory.
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
I am work on SQL Server 6.5
When I want to drop a view then appear error message :
"Msg 3701, Level 11, State 1 Cannot drop the view 'EUL_ODBC_SCHEMAS',
because it doesn't exist in the system catalogs."
Has anybody else experienced this, and do you know what is causing the warning?
Thanks in advance for any replies.