SQL 2012 :: Script To Check DB Names Against Jobs

Aug 1, 2014

As part of my backup routine, I have a SQL job for each DB which which is called "DB-NAME - Backup Job"

I need to put a script together to check that each database has a backup job associated to it.

select * From sys.sysdatabases where name not in ('msdb','model', 'master', 'Tempdb', 'DBA') order by name desc

select * from msdb.dbo.sysjobs order by name desc

I know all the details i need are in there, but i cant figure out the best way to tackle it. Do I need to do a cursor to go through each DB name, put it into a variable, then select the job name where name like '$variable%' ?

View 7 Replies


ADVERTISEMENT

Newbee Help Needed, I Need To Find Column Names If Any After 2 “check” Columns.

Sep 15, 2002

I need to find column names if any after 2 “check” columns.

Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.

So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.

Any help would be greatly appreciated.
Cheers
Henrik.

View 3 Replies View Related

DB Engine :: How To Check Names Of Running Instances Without Connecting To Server

Jul 3, 2015

When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.

View 12 Replies View Related

SQL 2012 :: Email For Over Running Jobs?

May 28, 2014

I'm trying to find out if there is a way within sql server or via script of emailing when a particular job or jobs has over run?

View 1 Replies View Related

SQL 2012 :: SSIS Jobs Not Using Newest One

Jul 10, 2015

I have just started to use Integration Services Catalog with SQL Server Agent. After I deploy a new version of the project the any Server Agent job that uses any of dstx still uses the old one. How do I fix this?

View 1 Replies View Related

SQL 2012 :: Getting Notification About Long Running Jobs?

Nov 5, 2015

We have a scheduled restore job daily from production backup to non-production. The job usually runs in 2 hrs but today it ran more than 8 hrs.

How to get notification about this job like if it runs more than 3 hrs send email with % of restore completed

View 0 Replies View Related

2012 / Setup Email To See Error Jobs?

May 26, 2015

I need to setup an email alerts with Jobs errors ( with error logs is it possible ) on nightly basis.

example:

Job name : Full backup | Status : Error | error log : 'The job failed 12........' 
Job name : Delta Backup| Status : Error | error log : 'The job failed 123........'
Job name : Cleanup DB  | Status : Error | error log : 'The job failed 1234........'  

What's the easiest way to do that ?

View 5 Replies View Related

SQL 2012 :: Changing SA Account Name Causes Maintenance Jobs To Fail

Sep 23, 2014

SQL 2012 Standard on Windows Server 2012 Standard

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

View 3 Replies View Related

SQL 2012 :: FROM Clause Have Same Exposed Names

Jul 2, 2014

We're getting

Msg 1013, Level 16, State 1, Line 1

The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries

CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST

[code]....

View 2 Replies View Related

SQL 2012 :: CAPS In Column Names

Aug 17, 2015

Is there a performance hit if I use CAPITOL LETTERS (like this) in column names for my SQL Server tables? Logically I would think that lower case letters would have better performance because they are smaller characters and easier to store.

View 4 Replies View Related

SQL 2012 :: Transfer All Jobs From One Instance To Another By Using Data Tools - SMO Connection?

Feb 12, 2014

I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

In order to solve this issue i have tried these solutions;

1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times

View 0 Replies View Related

SQL 2012 :: Retrieve Job History For All The Jobs Using Central Management Server?

Apr 2, 2015

Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?

View 0 Replies View Related

SQL Server 2012 :: How To Update Fields Having Same Names And DOB

Apr 11, 2014

Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)

insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223

[Code] .....

/* Expecting Output */

select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union

[Code] .....

View 5 Replies View Related

SQL 2012 :: Aliasing Column Names In Server

Jan 6, 2015

Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.

View 2 Replies View Related

SQL 2012 :: Find All Table Names That Have Foreign Key

Oct 20, 2015

Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])

Note: The table def below has a foreign key

IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]

GO

View 2 Replies View Related

SQL 2012 :: Replacing Column Names With Values

Nov 4, 2015

I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.

Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.

View 3 Replies View Related

SQL 2012 :: Ability To View Server Level Logins And Agent Jobs?

Sep 16, 2014

In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".

View 2 Replies View Related

SQL Server 2012 :: How To Set Some Restriction On Table Names While Creating

Nov 26, 2013

I need to ensure some naming standards to users while creating tables.

All table name should be in same pattern

(ie., ) TeamName_EmpId_tablename

I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.

View 5 Replies View Related

SQL 2012 :: Long Path Names To Files On Installation

Dec 13, 2013

I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.

View 8 Replies View Related

SQL Server 2012 :: Find Names Of Tables For A Particular User

Feb 18, 2015

how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.

View 1 Replies View Related

SQL Server 2012 :: Dynamic Query On DB Table Names

Mar 9, 2015

I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.

declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)

[Code] .....

View 1 Replies View Related

SQL 2012 :: Database Mail - Test Email Works But Jobs Won't Send Out Notifications

Oct 19, 2015

I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.

I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."

I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.

View 2 Replies View Related

SQL 2012 :: Query To Understand Names Of All Available Tables / Number Of Records

Aug 31, 2014

SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?

View 4 Replies View Related

SQL Server 2012 :: Replace Names Found In XML Field Name As Hyperlinks

Nov 18, 2014

I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.

ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

[code]...

for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.

View 1 Replies View Related

SQL 2012 :: Searching For Table Names In SSRS Reports In SharePoint

Jul 23, 2015

I am trying to query the SharePoint database to get a list of how many times a table name is being used in our SSRS reports.Here is my current query:

DECLARE @TEXTTOSEARCH AS VARCHAR(200)
SET @TEXTTOSEARCH = 'tablenamehere'
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)

[code]...

This works for some tables since the names are unique and I can just do a search for that. However, some of the table names are simple and can also be used as aliases.if there was some way to get the list of table names easier.

View 0 Replies View Related

SQL 2012 :: How To Check RI Integrity

Jun 1, 2015

I have a database in the 3rd normalized form. There is a need to load rows into a child table. To avoid having to drop RI, an ALTER TABLE WITH NOCHECK is being used. I am looking for a way to verify the integrity after the load is complete. After the load an ALTER TABLE WITH CHECK will be executed. Can I used DBCC to verify, that no orphaned child rows were loaded? If so, which parms would I have to use with DBCC?

View 0 Replies View Related

SQL Server 2012 :: Update Temp Table Where The Column Names Are Unknown

Dec 26, 2013

In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

how can I get the column names of a Temp table dynamically while doing an Update?

View 4 Replies View Related

SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?

Jan 21, 2014

How do I add column names as Total and SubTotal for NULL values.

SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)

[code]....

View 9 Replies View Related

SQL Server 2012 :: Case Statement - Populate Field Names In Table

Apr 9, 2014

Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE

case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'

View 1 Replies View Related

SQL 2012 :: How To Check Execution Plan Of A CTE

Jul 4, 2014

We have got a query for fine tuning and it is using lot of CTE ,how can i check the execution plan of that?

CREATE VIEW Mercy
AS
with ADR
as
(
SELECT urpx.RoleID ,
urx.UserID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
WHERE PermissionID = '1'
),
SDR
as
(

-- Collect the roles that a configured with Sales Team Create permission

-- This will include Sales Director , Suite Admin,

SELECT urpx.RoleID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
LEFT OUTER JOIN ADR ON ADR.UserID = urx.UserID
WHERE ADR.RoleID IS NULL
AND PermissionID='2'
)

View 6 Replies View Related

SQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not

Mar 23, 2014

I want to know, Is it possible to check a particular stored procedure is recompiling or not?

View 7 Replies View Related

SQL Server 2012 :: How To Check Which Row / Column Truncated

May 16, 2014

Table Temporary (Staging) - 400.000 rows and 200 columns.

When i done insert from temporary table to target, i have this:

Msg 8152, Level 16, State 13, Line 9

String or binary data would be truncated.

The statement has been terminated.

How can i check which row/column truncated?

View 9 Replies View Related

SQL 2012 :: Anyway To Check If Server Is Having Disk Latency

Oct 1, 2014

Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log

Date10/1/2014 8:28:58 AM
LogSQL Server (Current - 10/1/2014 12:00:00 AM)

Sourcespid10s

Message
SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved